July 14, 2008 at 10:12 am
I'm new to triggers, so bare with me.
I have a Customer Table that I want to create a trigger for. This trigger will check to see if a row was updated, and if it was, I want it to copy the row that was changed to an AuditCustomer table. we will use this later for a SQL report.
Whats the best way to do this?
July 14, 2008 at 10:18 am
Bare with you?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 14, 2008 at 10:22 am
Here are some links which might help...
http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 14, 2008 at 10:25 am
If I edit it will you answer my question?
July 14, 2008 at 12:08 pm
Here are some articles with data on how to create such triggers, and other options you have on the subject:
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 12:12 am
You simply need to create the UPDATE trigger on Customer table which includes Insert statment for AuditCustomer, Inserts the record from inserted / deleted table ( inbuild table in database triggers )
It depends how you want to maintain the audit like for OLD-NEW or only OLD record.
July 16, 2008 at 6:34 am
[font="Verdana"]
Abhijit (7/16/2008)
You simply need to create the UPDATE trigger on Customer table which includes Insert statment for AuditCustomer, Inserts the record from inserted / deleted table ( inbuild table in database triggers )It depends how you want to maintain the audit like for OLD-NEW or only OLD record.
Also need to add IF UPDATE condition before insert statement as Brad needs Trigger to be fired in case of UPDATE.
Mahesh[/font]
MH-09-AM-8694
July 16, 2008 at 7:17 am
Thanks guys, got it working. Now to write the SSRS reports.....:D
July 16, 2008 at 8:22 am
Hi Mahesh,
I am using, If update(column name) as it is one of the best way to find out whther the column is being updated. But suppose the table has like 20 columns. And we have to audit every single table in the database, is it efficient to use If update for all the columns for all tables to audit the update?. Is there any generic way and simple way where we can use the same trigger for any table instaead of hard coding the column name?
Thanks in advance.
July 16, 2008 at 8:37 am
[font="Verdana"]
mailsar (7/16/2008)
Hi Mahesh,I am using, If update(column name) as it is one of the best way to find out whther the column is being updated. But suppose the table has like 20 columns. And we have to audit every single table in the database, is it efficient to use If update for all the columns for all tables to audit the update?. Is there any generic way and simple way where we can use the same trigger for any table instaead of hard coding the column name?
Thanks in advance.
I guess COLUMNS_UPDATED() clause can be used in your case. Not sure and even not tried yet.
Mahesh[/font]
MH-09-AM-8694
July 17, 2008 at 11:50 am
Hello,
I havent actually tested this but my belief is that a trigger can still fire even if the same value is re-entered into the field.
I usually do something like
IF UPDATE(firstname)
BEGIN
IF inserted.firstname <> deleted.firstname
BEGIN
.... something like this for an after update trigger
Can someone corrrect me?
----------------------------------------------------
July 17, 2008 at 11:57 am
Yes, an update trigger will fire even if the update sets the data to the same thing as it was before.
You can handle that with a join between "inserted" and "deleted" on the PK (doesn't change) with an inequality test between the columns you're concerned about.
if exists
(select *
from inserted
inner join deleted
on inserted.ID = deleted.ID
and inserted.Col1 != deleted.Col1)
(Just to expand on what you wrote in your example.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2008 at 4:30 pm
Hi,
The update trigger will trigger even if the record is actually not updated right. Can we stop the update to the main table if the update dint actually happen. As in my application, if the user dint really change anything but still hit 'save', i need to display 'no changes made' and shud not go to update stored proc. I thought i can use a trigger for that but the trigger will trigger only after update. But the update gets commited only after trigger right. So can i use a trigger to stop the update and if it really updates another trigger to audit the update... Too confusing right :w00t:.. i know this is not possible in sql server to stop the update unless we do it in the front end application :).. Got it
Thankyou guys
July 24, 2008 at 8:15 am
You can have the trigger test for updated rows where the ID (or other PK) is the same, but other columns (even just one) are not the same. That means something was updated. If it doesn't find such rows, then there was no actual change.
The sample in my prior post does that kind of test.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply