Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger to Track Table Updates Expand / Collapse
Author
Message
Posted Tuesday, October 14, 2008 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:48 PM
Points: 8, Visits: 66
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.
Post #585735
Posted Tuesday, October 14, 2008 1:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
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:

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



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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #585749
Posted Tuesday, October 14, 2008 4:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:48 PM
Points: 8, Visits: 66
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
Post #585865
Posted Wednesday, October 15, 2008 12:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #586447
Posted Wednesday, October 15, 2008 3:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:48 PM
Points: 8, Visits: 66
Living up to your Hall Of Fame ranking :)
Post #586614
Posted Wednesday, October 15, 2008 3:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #586620
Posted Wednesday, October 15, 2008 5:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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. :P


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #586650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse