December 9, 2013 at 5:07 am
I have created audit table
CREATE TABLE Test_Audit
(column_name varchar(50),
old_value varchar(100),
new_value varchar(100),
Table_name varchar(100),
Username varchar(100),
action varchar(100),
Audit_Timestamp datetime
)
and I have table named testing
create table testing
(number int, name varchar(10))
I want trigger for insert,delete,update for table testing and the entries should be added to Test_Audit..
could anyone please help me with the queries??Your help is appreciated
December 9, 2013 at 7:33 am
That's a pretty general question, but rest easy! The syntax for triggers is fairly simple for what it seems like you're trying to do. BOL and Google will provide a lot more information more quickly regarding any general questions you have about syntax and if you run into any specific issues getting it to work, post back here and I'm confident we can work through it. General concepts are that there ate two tables you can reference inside of a trigger during an UPDATE(which you can think of as specialized delete and insert when dealing with triggers. These tables are "DELETED" and "INSERTED" and you can reference them like any other table for the most part. For an insert there is no DELETED table and for a delete, there is no INSERTED table. Triggers don't return data, so if you need messages passed back to something use RAISEERROR and your favorite flavor of error trapping. You can also split out the AFTER INSERT, AFTER UPDATE, and AFTER DELETE sections to customize any differences in actions between them. The above is very simplified, but it should get you started.
--Not tested of course...just threw this out here to give you an example
--of what a simple trigger might look like
CREATE TRIGGER tgTableTesting_InsUpd
ON TableTesting
AFTER INSERT,UPDATE, DELETE
AS
INSERT TestAudit (col1, col2...colN)
SELECT value1, value2...valueN from INSERTED
December 9, 2013 at 10:09 am
In my audit table there are columns as old value and new value
How to fetch the old value??and if the original table has 2 or 3 column names,how to fetch the result of old value and new value in single column??
December 9, 2013 at 10:14 am
Use those DELETED/INSERTED tables I mentioned.
as a simple example,
"SELECT deleted.column1 from DELETED" would get you the old value while "SELECT inserted.column1 from INSERTED" would get you the new value.
December 9, 2013 at 10:24 am
Greg A Goss (12/9/2013)
Use those DELETED/INSERTED tables I mentioned.as a simple example,
"SELECT deleted.column1 from DELETED" would get you the old value while "SELECT inserted.column1 from INSERTED" would get you the new value.
I'm going to put a caveat in here. Before you actually implement this in a production system you really need to performance test it. If you're tracking every column change for every row that you insert, update, or delete, then you're going to end up with a LOT of data in your audit table. If the table you are auditing is large and experiences a lot of data movement, then your audit table will grow very large and you could potentially be adding a ton of inserts to the audit table for each updated row in your source table. Make sure that this is what you want before you implement. You might want to consider only tracking general inserts and deleted only capturing the primary key for reference. Also on updates, you might want to consider only tracking a few columns. If all you're interested in is who is changing things and not necessarily what they are changing, then adding a few columns like modifyDate and modifyUser to the source table to track who changed various records over time (or have your audit table comprised of primaryKey, modifyUser, modifyDate).
Just a few ideas to make sure you're on the right track before you get too deep into implementing this.
-Greg Goss
December 9, 2013 at 10:34 am
Hi Greg,
Thanks for ur help
but "SELECT deleted.column1 from DELETED" would get you the old value while "SELECT inserted.column1 from INSERTED" would get you the new value...this only works for 1 column table
If i hav 4 column table,and all the 4 columns are changed during insert statement then old values and new values should be captured in audit table..How to do it??
December 9, 2013 at 10:49 am
That depends on if you're going wide or deep with your auditing. Again, I'm just typing stuff off the top of my head, so don't necessarily trust my syntax or logic. But there are plenty of detailed online examples depending on which way you want to go.
You could have a table like this (wide)
primaryKey, col1_old, col1_new, col2_old, col2_new...colN_old, colN_New, timestamp, user
....then you could capture any/all changes in one trigger query with a single insert to the audit table.
or you could go like this (deep):
columnName, OldValue, NewValue, tiemstamp, user
Which would only capture the data that has changed, but would be a lot of inserts and you'd have to write something like this for each column:
if deleted.column1Value <> inserted.Column1Value
insert auditTable (columnName, oldValue, newValue, timestamp, user)
values ('ColumnName", deleted.column1Value, inserted.Column1Value, getdate(), userName)
-Greg Goss
December 9, 2013 at 4:06 pm
If you're using Enterprise Edition, just use Change Data Capture and let SQL do all the hard work for you.
If not, the overhead of what you want to do is huge. To reduce the overhead as much as possible, you should dynamically generate static triggers. That is,write code that generates a static trigger based on the table's current definition. When a table column(s) is(are) added/deleted, then re-run the code to re-gen the triggers to replace the existing triggers.
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".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply