asked
Nested Triggers not working
Hello all I have a table where users add records (TableA) I have an Insert Trigger on TableA that creates multiple Records in TableB based on the Primary Key (TAPK) of TableA, the Primary Key of TableB is TBPK. I Have an Insert Trigger on TableB which Creates multiple Records in TableC based on the Primary Key (TBPK) of TableB. All records are created successfully but the trigger for tableB (Which creates the Records in TableC) does not seem to calculate the fields properly. Table B Trigger is (Simplified) ALTER TRIGGER trig_CreateSuggestions ON dbo.Tbl_AreaT FOR INSERT AS BEGIN INSERT INTO dbo.Tbl_CAllocations ( [FK_SplitID] , [FK_URN] , [Implant] , FK_TID , [AdditionalComments] , [Outcome] , [Outcome_Status] , [C_ES_No] ) SELECT DISTINCT i.splitID , p.URN , 0 , i.TaskingID , '' , '' , '' , ( SELECT COUNT(*) FROM dbo.Tbl_T_Establishments TE JOIN dbo.Tbl_C_Est AS CE ON te.FK_EST_ID = CE.FK_EST_ID WHERE TE.T_ID = i.TID AND CE.FK_URN = p.URN ) FROM inserted i JOIN tbl_people p ON 1 = 1 JOIN dbo.Tbl_C_Handles AS h ON p.URN = h.FK_URN JOIN dbo.Tbl_Organs AS o ON h.cno = o.CNo WHERE o.Area = i.area END Problem: The calculated field works fine if I manually add a record to tableB, but when it is added by the trigger on TableA the calculation results in 0 which I know is not correct. Could anyone resolve this for me please? Thank you
** > I AM SUCH A FOOL!!! ** Thanks for your help but I have just realised the problem and its nothing to do with the triggers, which were working fine. It was down to the order I was populating the database. Thank you all for trying anyway.
Are nested triggers configured as allowed? Configure the nested triggers Server Configuration Option:
http://technet.microsoft.com/en-us/library/ms178101.aspx
Thanks for the reply Yes nested triggers are enabled, the strange thing is that sql does created the related records correctly, its the calculation that is wrong.
This doesn't answer your question, but If I was faced with this.... Instead of having this logic buried in nested triggers, wouldn't this be better in the proc/sql that kicks all this off insert into TableA insert into TableB select data from TableA based on TAPK insert into TableC select data from TableB where FK = TAPK (should be the same as data based on TBPK)
14 People are following this question.
