April 16, 2008 at 8:45 am
Hi there,
I have an after insert trigger that evaluates the value of a field in the Inserted 'magic table' and updates the same field in the base table to which the trigger is attached when the value is equal to certain values defined in a case statement.
When run in isolation in a query using the base table in place of Inserted, this code performs as expected but does not seem to run when invoked in my trigger.
I have previously used triggers that use the Inserted table so I am unsure as to why this code won't work.
Here is my code:
USE [GuidanceNavigationTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[AnalyseAccountCode] ON [dbo].[NL_TRN_TEMP]
FOR INSERT AS
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(p.PMA_ANAL_FIELD10, '99') COLLATE Latin1_General_CI_AS -- Product group
+ '4'
ELSE
i.NL_JOURNAL_ACCOUNT
END
FROM Inserted i
LEFT JOIN [ASC].dbo.WOR_TBL AS w
ON i.NL_DETAIL_JNL_REFERENCE = w.WOR_ORDER 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 NL_TRAN_ID,
NL_DETAIL_LINE_NO,
isnull(NCODE, 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
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
Any help or advice would be greatly appreciated.
April 17, 2008 at 9:54 am
Well I spent two days on this and couldn't get the trigger to work even though the code works fine when run outside of my trigger.
Eventually I simply moved the code to a stored procedure and had the trigger call the procedure which then worked directly on the base table rather than the INSERTED psuedo-table. This solved the issue.
Even though the problem is resolved I would very much appreciate any insights into why my trigger didn't function as I expected in case I run into this issue in the future.
Thanks,
Chris
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply