SQL Server Performance Forum – Threads Archive

Newby Question re Triggers

Hi All, I will apologise up front if this is a little long winded but I would prefer to provide more info than less. Firstly, my background is with a record based 4GL rather than the set based approach of SQL, so this has been a roller-coaster ride just to grasp some of the concepts of SQL….but I’m getting there [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. The problem I have is that I have created some triggers and though they are working, they are not doing exactly what I would like. I will try to make this as generic as I can…. <ul> <li>There re two tables A and B.</li> <li>There is a one-to-one relation between them.</li> <li>A Flag field in table A determines updates that occur for table B.</li> <li>Triggers have been defined for table A to manage the updates to table B.</li> <li>There re two tables A and B.</li> </ul> Following is an example of the Insert Trigger: <Standard Trigger definition stuff> SET NOCOUNT ON DECLARE @FlagField AS INT SELECT @FlagField = FlagField FROM INSERTED /* Test condition for update to table B */ IF @FlagField <= 0 RETURN /* OK table B is to be updated. */ /* Update is via a Stored Proc */ EXEC <Stored Proc> SET NOCOUNT OFF PROBLEM When the RecordSet for table A contains more than one record, the trigger only fires for the LAST record in the set. Why[?]

I cant immediately see why the trigger wouldnt fire. You say ‘standard trigger definition stuff’ but this is perhaps one of the important bits to quote. Do you also have a trigger for updating (you refer to table B being updated, but Im assuming this means the table is updated by inserting new records). But the main reason im posting is to see if you really need a trigger at all. Would it not be possible to just create a stored procedure to do this ? If not, and youre on sql 2000, you might also like to check INSTEAD OF triggers, I dont know if you already have.

I agree with Chappy, a stored procedure, which updates both tables essentially at the same time, would be more efficient, and easy to code and debug.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

I think I may know the reason. Triggers only fire once per update/insert statement, or as Microsoft says, only once per data modification event. For example, you perform a mass update on 1,000 records, your UPDATE trigger will only fire once not 1,000 times.
———-
T Kelley
MS, MCDBA, OCA, CIW

SELECT @FlagField = FlagField
FROM INSERTED
The problem here is that the INSERTED table contains ALL the rows inserted or updated. If you do a batch insert or update, there will be more than one row. If you need to perform different actions based on the value of a flag field, you will need to re-think your logic. As brad and chappy have suggested, a stored procedure might be a better option, although I personally like triggers when used properly. You might want to consider using a cursor (yuck!) to loop through all the records in INSERTED and call your stored proc for each row. If all updates to table A all occur through a single stored procedure, then you can modify that stored proc to update table B as well and avoid triggers altogether. However, if updates can happen via views or from several places, the trigger is the best option. It also depends on how much work is being done in the stored proc you are calling from the trigger…

]]>