October 14, 2008 at 1:09 pm
I am very new to triggers, basically we have a table which has the columns of Update_Date and Update_By.
I want to have a trigger on this table to update two columns as follows:
Set Update_By = User(), Update_Date = Getdate(), What_Upated = Name of the column which was updated or "New Insert" if a new record is inserted
After these columns are updated, I then need the trigger (or a separate trigger if that is the only way) to copy all columns from this table into a tracking table.
Any assistance would be greatly appreciated.
October 14, 2008 at 1:42 pm
I'll start by answering your last question first. You can do it all in one trigger if you want. The second part is a a little more complex as there is no simple way of knowing what was updated. Because the COLUMNS_UPDATE and UPDATE() functions available within the trigger will show a column as updated even if the data has not changed. So a statement like this:
Update table
Set ColumnA = ColumnA
Will show ColumnA as being updated even though you made no changes to the data. Also does changing 'jack' to 'Jack' qualify as an update? In most SQL Server installations comparisons are case insensitive so a simple compare won't show the difference unless you change the collation.
All that being said here is a skeleton to start you out:
[font="Courier New"]CREATE TRIGGER trg_table_name_ins_upd
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON
-- now do the updates
UPDATE table_name
SET Update_By = SUSER_SNAME(),
Update_Date = GETDATE(),
What_Updated = CASE
-- this gets the inserts as there will be no deleted row
WHEN D.pk_column IS NULL THEN 'New Insert'
ELSE -- now we do the updates and you have to compare
-- each column in inserted to the same column in deleted
-- I concatenated because you could update more than 1
-- column in a single update
CASE
WHEN I.column1 <> D.column1 THEN 'Column1, '
ELSE ''
END +
CASE
WHEN I.column2 <> D.column2 THEN 'Column2, '
ELSE ''
END +
...
END
FROM
inserted I LEFT JOIN
deleted D ON
I.pk_column = D.pk_column
WHERE
I.pk_column = table_name.pk_column
-- now insert into audit table
INSERT INTO audit_table
SELECT
CASE
WHEN D.pk_column IS NULL THEN 'Insert'
ELSE 'Update'
END AS action,
COLUMN list
FROM
inserted I LEFT JOIN
deleted D ON
I.pk_column = D.pk_column
RETURN
[/font]
You may want to check out this article: http://www.sqlservercentral.com/articles/Triggers/64214/ And the 2 articles written by GSquared on auditing and logging which you can find here: http://www.sqlservercentral.com/Authors/Articles/GSquared/475322/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 4:23 pm
Thank you very much for the quick reply, and those are excellent articles, thanks for highlighting them.
I have done some exploring on other table triggers already set up in our system from previous staffing, and they seem to do what I read here as a "no no" of only being able to capture row by row changes rather than any bulk changes.
They actually hard coded each column:
Create Trigger Trigger_Name on Table_Name
For Update As
If UPDATE (Column_Name)
BEGIN
UPDATE Table_Name
Set Update_By = Current_User,
Update_Date = 'GetDate(),
What_Updated = 'Column_Name'
FROM Table_Name inner join inserted on Table_Name.ID = inserted.ID
END
If UPDATE (Column2_Name)
BEGIN
etc
And they do this for every column.
This seems to very much go against your article talking about working for single row updates, but not mass updates/inserts of data to the table.
And they have a separate Insert trigger
CREATE TRIGGER_Insert ON Table_Name
FOR INSERT
AS
BEGIN
UPDATE Table_Name
Set Update_By = Current_User,
Update_Date = GetDate(),
What_Updated = 'Inserted'
FROM Table_Name inner join inserted on Table_Name.ID = inserted.ID
END
Yours obviously look a lot more efficient, so thanks again.
I am testing this out using our tables and ran into an error of
Error 156: Incorrect syntax near the keyword 'END', incorrect syntax near the keyword 'COLUMN'
It also has a syntax error by keyword 'AFTER', but I solved that by including the On Table_Name after
CREATE TRIGGER trg_table_name_ins_upd on Table_Name
Guess I should have mentioned, we are using SQL2000 currently (although we will be upgrading to 2005 shorlty; too bad they wouldn't let us go straight to 2008, but they are too cheap here :hehe:
October 15, 2008 at 12:21 pm
Glad I could be of help. Sorry about the errors, the second one you mention was a brain cramp on my part. The first one probably has something to do with the fact that I used pseudocode.
There is nothing wrong with having the insert trigger and an update trigger, it doesn't hurt anything and maybe it makes what happens clearer.
The triggers you found are set-based, but they check the individual columns for update which I think would be slower and is going to issue multiple updates if more than one column was changed so the last one hit will be the one you see in What_Updated column even if 3 were changed at that time. It also says a column was updated even when the data was not changed, while my code only logs actual changes to the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 3:45 pm
Living up to your Hall Of Fame ranking 🙂
October 15, 2008 at 3:55 pm
Thanks, but you only get there by being prolific not necessarily correct. In this case I happen to be right, but if you answer enough questions you have to get some right. Plus you learn things too.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 5:01 pm
Jack Corbett (10/15/2008)
Thanks, but you only get there by being prolific not necessarily correct.
Thank goodness too, or I never would have gotten there!
Heh. 😛
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply