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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy