Problem with after insert trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with after insert trigger

Author

Topic

ChrisKGuidance
Starting Member

2 Posts

Posted - 2008-04-17 : 08:20:21

Hi,

I have an after insert trigger on a table that is supposed to update the value of a field in the table when it matches certain values.

When I insert rows into the base table the trigger fires but does not catch and replace the values I want it to.

I have tested my code in a query and it works as intended but it doesn't when I put it in the trigger.

Here is my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[AnalyseAccountCode]
ON [dbo].[NL_TRN_TEMP]
AFTER INSERT
AS
BEGIN
UPDATE NL_TRN_TEMP
SET NL_JOURNAL_ACCOUNT = Analysis.ACCOUNT
FROM NL_TRN_TEMP,
(
SELECT i.NL_TRAN_ID,
i.NL_DETAIL_LINE_NO,
ACCOUNT =
CASE i.NL_JOURNAL_ACCOUNT
WHEN 'Z42000' THEN
'Z421'
+ isnull(ProjectType.CAN_DESC, '0') COLLATE Latin1_General_CI_AS
+ isnull(MarketType.CAN_DESC, '0') COLLATE Latin1_General_CI_AS
WHEN 'Z91994' THEN
'Z91'
+ isnull(PMA_ANAL_FIELD10, '99') COLLATE Latin1_General_CI_A+ '4' ELSE
i.NL_JOURNAL_ACCOUNT
END
FROM Inserted i
LEFT JOIN [ASC].dbo.WOR_TBL AS w
ON w.WOR_ORDER = i.NL_DETAIL_JNL_REFERENCE COLLATE Latin1_General_CI_AS
LEFT JOIN [ASC].dbo.CAN_TBL AS ProjectType
ON ProjectType.CAN_TYPE = 'T'
AND ProjectType.CAN_NUMBER = 2
AND ProjectType.CAN_CODE = w.WOR_ANAL_FIELD2
LEFT JOIN [ASC].dbo.CAN_TBL AS MarketType
ON MarketType.CAN_TYPE = 'T'
AND MarketType.CAN_NUMBER = 3
AND MarketType.CAN_CODE = w.WOR_ANAL_FIELD3
JOIN [ASC].dbo.PMA_TBL AS p
ON p.PMA_PART_ONLY = w.WOR_ITEM_ONLY
AND p.PMA_PART_REV = w.WOR_ITEM_REV
) Analysis
WHERE NL_TRN_TEMP.NL_TRAN_ID = Analysis.NL_TRAN_ID
AND NL_TRN_TEMP.NL_DETAIL_LINE_NO = Analysis.NL_DETAIL_LINE_NO

UPDATE NL_TRN_TEMP
SET NL_JOURNAL_ACCOUNT = Analysis.ACCOUNT
FROM NL_TRN_TEMP,
(
SELECT i.NL_TRAN_ID,
i.NL_DETAIL_LINE_NO,
isnull(n.NCODE, i.NL_DIMENSION2) AS ACCOUNT
FROM Inserted i
LEFT JOIN NL_ACCOUNTS n ON n.NCODE = i.NL_JOURNAL_ACCOUNT
) Analysis
WHERE NL_TRN_TEMP.NL_TRAN_ID = Analysis.NL_TRAN_ID
AND NL_TRN_TEMP.NL_DETAIL_LINE_NO = Analysis.NL_DETAIL_LINE_NO

UPDATE NL_TRN_TEMP
SET NL_JOURNAL_ACCOUNT = Analysis.ACCOUNT
FROM NL_TRN_TEMP,
(
SELECT i.NL_TRAN_ID,
i.NL_DETAIL_LINE_NO,
ACCOUNT =
CASE WHEN i.NL_JOURNAL_ACCOUNT = '9999' AND n.NCODE IS NOT NULL THEN
i.NL_DIMENSION2
ELSE
i.NL_JOURNAL_ACCOUNT
END
FROM Inserted i
LEFT JOIN NL_ACCOUNTS n
ON n.NCODE = i.NL_DIMENSION2
) Analysis
WHERE NL_TRN_TEMP.NL_TRAN_ID = Analysis.NL_TRAN_ID
AND NL_TRN_TEMP.NL_DETAIL_LINE_NO = Analysis.NL_DETAIL_LINE_NO
END

Any help would be greatly appreciated.

Thanks,

Chris

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 07:50:42

i vaguely remember having encountered this and this is because an update is a delete and insert transaction...

since you're updating inside the table that has been inserted into... you need to check the columns when doing the update so they get excluded from the trigger.. this is like trigger within a trigger in 2000...

like I said, I am not 100% sure...

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 08:54:01

You are updating the table that just got inserted?
Try an INSTEAD OF trigger.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 08:57:36

Your three updates can also be replaced with this single one

UPDATE		NL_TRN_TEMP
SET		NL_JOURNAL_ACCOUNT = Analysis.ACCOUNT
FROM		NL_TRN_TEMP
INNER JOIN	(
			SELECT		i.NL_TRAN_ID,
					i.NL_DETAIL_LINE_NO,
					COALESCE(
						CASE
							WHEN i.NL_JOURNAL_ACCOUNT = '9999' AND n.NCODE IS NOT NULL THEN i.NL_DIMENSION2
							ELSE i.NL_JOURNAL_ACCOUNT 
						END,
						m.NCODE,
						i.NL_DIMENSION2,
						CASE i.NL_JOURNAL_ACCOUNT
							WHEN 'Z42000' THEN 'Z421' + isnull(ProjectType.CAN_DESC, '0') COLLATE Latin1_General_CI_AS + isnull(MarketType.CAN_DESC, '0') COLLATE Latin1_General_CI_AS
							WHEN 'Z91994' THEN 'Z91' + isnull(PMA_ANAL_FIELD10, '99') COLLATE Latin1_General_CI_A + '4'
							ELSE i.NL_JOURNAL_ACCOUNT 
						END AS ACCOUNT
					) AS ACCOUNT
			FROM		Inserted i
			LEFT JOIN	[ASC].dbo.WOR_TBL AS w ON w.WOR_ORDER = i.NL_DETAIL_JNL_REFERENCE COLLATE Latin1_General_CI_AS
			LEFT JOIN	[ASC].dbo.CAN_TBL AS ProjectType ON ProjectType.CAN_TYPE = 'T' 
						AND ProjectType.CAN_NUMBER = 2 
						AND ProjectType.CAN_CODE = w.WOR_ANAL_FIELD2
			LEFT JOIN	[ASC].dbo.CAN_TBL AS MarketType ON MarketType.CAN_TYPE = 'T'
						AND MarketType.CAN_NUMBER = 3 
						AND MarketType.CAN_CODE = w.WOR_ANAL_FIELD3
			INNER JOIN	[ASC].dbo.PMA_TBL AS p ON p.PMA_PART_ONLY = w.WOR_ITEM_ONLY
						AND p.PMA_PART_REV = w.WOR_ITEM_REV
			LEFT JOIN	NL_ACCOUNTS m ON n.NCODE = i.NL_JOURNAL_ACCOUNT
			LEFT JOIN	NL_ACCOUNTS n ON n.NCODE = i.NL_DIMENSION2
		) Analysis ON NL_TRN_TEMP.NL_TRAN_ID = Analysis.NL_TRAN_ID 
WHERE		NL_TRN_TEMP.NL_DETAIL_LINE_NO = Analysis.NL_DETAIL_LINE_NO

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources