March 8, 2013 at 3:25 pm
There is a flag column that basically indicates that record changed since the last "housecleaning".
Can I create a trigger on the table that would set that flag "True" just for that particular record that changed?
Thank you
March 8, 2013 at 3:45 pm
You could but wouldn't it be easier to set that flag to true during the "houselceaning" procedure for that record?
March 8, 2013 at 4:11 pm
"Housecleaning" in this case is in part resetting this flag to 0 everywhere... Apologize if using this word creates a confusion, I should probably call it a "checkpoint" or something.
The idea again is that at point A in time all the records have field Flag = 0, which means that they were successfully processed
Then whenever record gets changed, the trigger fires and updates Flag = 1 for that same record
So that at time point B only those with Flag = 1 would be processed
Thank you
March 8, 2013 at 5:14 pm
Sure, that's easy, as long as you have unique column(s), such as an IDENTITY column, you can use to identify a specific row in the table. SQL provides a "virtual" table called "inserted" that will contain only the row(s) that were updated by the statement that fired the trigger:
CREATE TRIGGER tablname__trg_UPDATE
ON dbo.tablename
AFTER UPDATE
AS
UPDATE tn
SET
flag_col = 1
FROM dbo.tablename tn
INNER JOIN inserted i ON
i.key_col = tn.key_col
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 8, 2013 at 6:02 pm
But wouldn't it create a recursive call on that trigger? Updating the same table that the UPDATE trigger is defined on?
Thank you
March 8, 2013 at 7:59 pm
btio_3000 (3/8/2013)
But wouldn't it create a recursive call on that trigger? Updating the same table that the UPDATE trigger is defined on?Thank you
No, not unless RECURSIVE_TRIGGERS is ON. It is OFF by default.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 9, 2013 at 7:42 am
opc.three (3/8/2013)
btio_3000 (3/8/2013)
But wouldn't it create a recursive call on that trigger? Updating the same table that the UPDATE trigger is defined on?Thank you
No, not unless RECURSIVE_TRIGGERS is ON. It is OFF by default.
Excellent point.
But if you're still worrried about recursive triggers, add the appropriate check(s) in the code:
CREATE TRIGGER tablname__trg_UPDATE
ON dbo.tablename
AFTER UPDATE
AS
IF NOT UPDATE(flag_col)
UPDATE tn
SET
flag_col = 1
FROM dbo.tablename tn
INNER JOIN inserted i ON
i.key_col = tn.key_col
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 11, 2013 at 1:01 pm
Got it
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply