Update Trigger Might Need to Be careful with INSERTED table?

[KirbyWallace]

I need to create an AFTER UPDATE trigger. I've seen it suggested that the record being affected will be determined by joining the table being updated with the internal "INSERTED" table that is provided.

Very briefly: UPDATE tbl_a where tbl_a.PrimKey = INSERTED.PrimKey.

My question is, is there likely to be other "INSERTED data" in the INSERTED table that has nothing to do with what I am doing? Is it possible that the INSERTED table contains data currently being inserted by other processes?

Or, is INSERTED a "private" table containing only the data that **I** am updating?

Thanks!

Kirby

 

[gmmastros]

Triggers are events that are fired under the right conditions. Since you are using an update trigger, ANY update to any data in the table will cause the update trigger to fire.

When you write trigger code, it is critically important that it can handle multiple rows.

My question is, is there likely to be other "INSERTED data" in the INSERTED table that has nothing to do with what I am doing?

Yes. Unless you are the only user in the system. Triggers don't care "who" caused them to fire, only "what" caused it.

Is it possible that the INSERTED table contains data currently being inserted by other processes?

Again, yes. Triggers do not discriminate.

If 2 users simultaneously update the same table at the same time, the trigger will fire twice (once for each user).

Ex:

User1:
Update People Set EyeColor = 'Blue' Where PersonId = 1

User2:
Update People Set ShoeSize = 9.5 Where PersonId = 1

In this case, the update trigger will fire twice, once for each update statement.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

Hi George...


Seems you are always the first one with the good stuff! Thanks.

Well, let me just be specific, then, and see if you can explain what I need to do to make this work. Here's my trigger:

CREATE TRIGGER Timesheet_Modification_Date_Timestamp
   ON  tbl_Employee_Timesheet
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE tbl_Employee_Timesheet SET [Modification_Date] = GETDATE()
	FROM tbl_Employee_Timesheet a
	INNER JOIN Inserted b ON (a.Timesheet_ID = b.Timesheet_ID)
	
END

As I understand it, this trigger will update the Modification_Date for everything pending update. Right? This in itself may not be so bad, I suppose. After all, they all need to get done, and if they are siting in the INSERTED magic table, then they are all going to get done within milliseconds of each other (I'm not high volume - there's not enough lag-time to be significant).

But it's not ideal.

What IS ideal? I saw another solution that used a timestamp to detect a change in the row, and then the afterupdate trigger updated the Modification_Date based upon selecting the timestamp that changed to determine the row to update. (that was a tongue twister...)

That example was done that way only to avoid an infinite-loop caused by an after update trigger causing an "after update" itself.

Would you recommend that approach?

Thanks...

 

[Emtucifor]

I think one thing you might be needing some guidance on is that the Inserted table only holds the rows for a single update at a time, and the trigger is fired once for every update.

So you never have to worry about rows from *other* updates being in your Inserted table inside of a trigger: it only has data from *this* update.

Infinite loops are not a problem unless you have multiple triggers on the same table that fire for the insert/update/delete pattern you use, because SQL Server does not fire the same trigger if that triggers modifies its own source table.

As for ping-pong triggers which can occur if two tables with triggers modify each other or a single table has two triggers, the number of recursive steps inward is a sever-level setting and (if I recall correctly) has a hard limit of 16 besides.

 

[gmmastros]

I'm not as familiar with triggers as I ought to be, but I would suggest that you use the Update function. If the Modification_Date is not updated, then update it. If it is updated, then don't update it. Clear as mud?

CREATE TRIGGER Timesheet_Modification_Date_Timestamp
   ON  tbl_Employee_Timesheet
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    [!]If Not Update(Modification_Date) [/!]
      Begin    
        UPDATE tbl_Employee_Timesheet SET [Modification_Date] = GETDATE()
        FROM tbl_Employee_Timesheet a
        INNER JOIN Inserted b ON (a.Timesheet_ID = b.Timesheet_ID)
      End
END

Give this a try. Let me know how you make out.

There is also a setting on the database that controls recursive triggers. In SQL Server Management Studio, right click your database, click properties. Click the Options tab. In the Miscellaneous section, there is a 'Recursive Triggers Enabled' option. On my database this is set to false. If this is set to true in your database, do not blindly set it to false. If you consider changing this value, make sure you read up on how it works and what it does. You should also check with other developers to see if they are relying on this functionality.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Server does not fire the same trigger if that triggers modifies its own source table.

There's a setting for that.

Select Databaseproperty(db_name(), 'IsRecursiveTriggersEnabled')

If the above query returns 1, then recursive triggers are enabled. In this case SQL Server would fire the same trigger if the trigger modifies its own source table.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emtucifor]

Thanks for correcting that, George. You're right, it is a setting.

My memory is telling me that there are actually two settings, one that controls whether a trigger modifying its own table refires the trigger, and one that controls total recursion depth, which affects the ping-pong trigger issue I mentioned earlier (or simply a chain of tables each inserting to the next). What I'm unclear on is whether the second setting works independently of the first, or not.

 

[gmmastros]

The Recursive trigger does not affect the ping-pong trigger effect.

From books on line:

SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

Recursive triggers enable the following types of recursion to occur:

Indirect recursion: With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

Direct recursion: With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emtucifor]

Mental note to self. Remember these two terms and their special meaning in SQL Server:

(Direct) Recursive triggers - same table/trigger
Nested triggers - different triggers (same or different table). aka indirect recursion.

 

[gmmastros]

Still, it's interesting to not that the code I posted earlier does solve the recursive trigger issue. What's weird is that if recursive triggers is enabled, the code I posted above will fire twice every time the data is updated.

The first time it fires should be no surprise. Since the trigger code is updating data in the same table that caused the trigger, the trigger fires a second time. But, since we are checking the modified_date column and only updating it if it's not updated, the code will not go in to the IF part of the code, and the table will NOT be updated again.

Suffice to say, the trigger will fire exactly twice and no more.

Now that I think about it, it wouldn't be a bad idea to check all the triggers in my database to make sure this problem cannot affect me. I mean, I have recursive triggers disabled, but I don't really like to rely upon a database setting which could be changed at any time. This is especially important to me since I sell a commercial application and don't always have complete control of the instance the database is attached to.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom