March 11, 2011 at 3:10 pm
Hi,
I am working on creating Triggers and wanted to know if SQL Server has something like a "Insert Before Update" Trigger. I have 2 tables and have to track all the changes to the table A. Therefore, I want to insert the entire row into a table B before the running the update command to table A.
Any suggestions will be appreciated.
March 11, 2011 at 10:28 pm
You will need to use an update trigger. In this trigger are two virtual tables (inserted and deleted). Inserted is the new values and deleted contains the values prior to the update. To create an audit of the data prior to the update you can get this from deleted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 7:54 am
Thanks for your reply. I was trying to work with what to told and I still have the same problem. The trigger works but the row insertion is done after the update and not before it.Here is what I am trying to do
ALTER TRIGGER [dbo].[trg_History] ON [dbo].[Amount]
FOR UPDATE
AS
INSERT INTO History
(ID,
Amount1,
Amounttype
)
SELECT
ID,
Amount1,
Amounttype
FROM
inserted (or if I give deleted)
March 17, 2011 at 8:12 am
Triggers in SQL are either Instead Of (replacing the statement) or After.
Why do you feel you need a before trigger? From what you have there, there should be no difference at all between running that before the update or after
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2011 at 8:17 am
abhishekc17 (3/17/2011)
Thanks for your reply. I was trying to work with what to told and I still have the same problem. The trigger works but the row insertion is done after the update and not before it.Here is what I am trying to doALTER TRIGGER [dbo].[trg_History] ON [dbo].[Amount]
FOR UPDATE
AS
INSERT INTO History
(ID,
Amount1,
Amounttype
)
SELECT
ID,
Amount1,
Amounttype
FROM
inserted (or if I give deleted)
I think you probably want to use deleted in this case. The inserted table will have the new values and deleted has the values before the update. Presumably in the history table you want to record the value it was before it got updated. The good news too is that you coded this so that it will handle multiple updates (and thus multiple rows in inserted/deleted) correctly which is a very common pitfall with triggers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 9:31 am
Hi Gail,
I just want to hold the changes of the row. If the row has no change then I don't want to populate the History table. Thats the reason when an update command runs on a row I want to record the old values before the update command updates the row.
March 17, 2011 at 9:44 am
What you want to do is compare the inserted and deleted tables. If there is no change, then no insert. If there is a difference, then insert something
So if I were looking for changes in amount1
ALTER TRIGGER [dbo].[trg_History] ON [dbo].[Amount]
FOR UPDATE
AS
INSERT INTO History
(ID,
Amount1,
Amounttype
)
SELECT
ID,
Amount1,
Amounttype
FROM
inserted i
inner join deleted d
on i.pk = d.pk
where i.amount1 <> d.amount1
March 17, 2011 at 9:45 am
To do that you will have to compare inserted and deleted. This will be the new and old values. If there are a lot of columns this may end up being more hassle than it is worth. I have always made the argument that tracking the update even though no values were changed actually provides some additional info. You know that somebody opened an edit screen and hit save without making any changes. May not be very helpful in the long run but it takes a lot less code and provides a more complete history.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 9:47 am
I used the inserted and deleted but the inserted row always has the new values rather than the old value. Not sure what I am doing wrong. It can't be this difficult.
Thanks for your help again.
March 17, 2011 at 9:49 am
inserted will ALWAYS have the new values and deleted will ALWAYS have the old values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 10:25 am
Sean,
I apologize for my previous post. The deleted works perfectly. Thanks you very much.
And thank you Steve..
March 17, 2011 at 10:28 am
abhishekc17 (3/17/2011)
Sean,I apologize for my previous post. The deleted works perfectly. Thanks you very much.
And thank you Steve..
No need to apologize you said nothing could be taken as anything but polite under any circumstances. 😀 Glad we could help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 10:51 am
You are welcome, and if you wouldn't mind, can you show the code that worked for you?
March 18, 2011 at 2:14 pm
There's another way to do this without joining Inserted to Deleted and it reads a little easier on the eye. This one gets the old values before the update. To get just the new values after the update switch inserted and deleted below:
INSERT INTO AuditTable
( Col1, Col2, Col3, Col4 )
SELECT
X.Col1, X.Col2, X.Col3, X.Col4
FROM
(SELECT Col1, Col2, Col3, Col4
FROM deleted
EXCEPT
SELECT Col1, Col2, Col3, Col4
FROM inserted
) AS X
This will only insert rows where there was a change. If you actually want both values (before and after) you will have to join Inserted to Deleted.
Todd Fifield
March 18, 2011 at 2:46 pm
The code which worked for me is extremely simple.
ALTER TRIGGER [dbo].[trg_History] ON [dbo].[Amount]
FOR UPDATE
AS
INSERT INTO History
(ID,
Amount1,
Amounttype
)
SELECT
ID,
Amount1,
Amounttype
FROM
deleted
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply