December 5, 2013 at 6:21 am
I am working on triggers for insert,update and delete
output of the trigger shows tablename,user and timestamp
Is it possible to display the column name,old value and new value??
There are around 100 tables with different column count..
I only want 1 trigger which will show output with columnname,old value and new value,,Please help
December 5, 2013 at 6:29 am
what do you mean by output from the trigger. do you want to fill log table from a trigger?
December 5, 2013 at 6:35 am
I have created a audit table which shows Tablename,timestamp when triggers happend and which tablename the trigger happend..
I want columnname,oldvalue,newvalue to be added in audit table...
It works well with 1 table but I have 100 tables with different columns..how to do it in single trigger which writes output to audit table with columnname,oldvalue,newvalue????
December 5, 2013 at 6:38 am
Here is the script
CREATE TABLE Test_Audit
(Table_name varchar(100),
Audit_Timestamp datetime
)
CREATE TRIGGER AfterInsert ON [dbo].[test]
FOR INSERT
AS
declare @Table_name varchar(100)
set @Table_name='test'
insert into Test_Audit
(Table_name,Audit_Timestamp)
values(@Table_name,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
December 5, 2013 at 6:40 am
navimir (12/5/2013)
I am working on triggers for insert,update and deleteoutput of the trigger shows tablename,user and timestamp
Is it possible to display the column name,old value and new value??
There are around 100 tables with different column count..
I only want 1 trigger which will show output with columnname,old value and new value,,Please help
you'd have to use the sys.columns to generate each custom trigger for you,you cannot make a single generic trigger that will auto-magically identify which columns changed; you have to generate stuff like 'WHERE INSERTED.Column1 <> DELETED.Column1' fromt eh metadata.
the data would be meaningless for auditng without the primary key of the table, though;
if you saw a million updates that Said
User: 'Lowell'
Table: 'Product'
Column: Color:
OldValue:'Blue'
NewValue:'Red'
how would that help you at all ? was it the same row a million times, or all rows at once?
remember a trigger is going to handle multiple rows, and now you want to unpivot the columns into rows in an audit table for 100 tables?
it's possible to do what you are asking . enabling CDC or SQL Auditing would actually be easier , and then you can simply query the CDC tables, instead of custom triggers.
Lowell
December 5, 2013 at 7:35 am
Could you plz let me know how to check in trigger for old value and new value so that the result can be added to audit table??
December 5, 2013 at 8:47 am
details. you'll wnat to provide details if you wantany meaningful answers.
inside the trigger, you compare the columns in the virtual tablees INSERTED and DELETED to each other.
ie
IF EXISTS (SELECT 1
FROM INSERTED
INNER JOIN DELETED
ON INSERTED.[ThePrimaryKeyOfTheable] = DELETED.[ThePrimaryKeyOfTheable]
WHERE INSERTED.[SpecificColumnName] <> DELETED.[SpecificColumnName] )
BEGIN
--do something / insert into audit table
PRINT 'The values dont match!'
END --IF EXISTS
and here's a crappy example:
USE tempdb;
GO
IF OBJECT_ID('[tempdb].[dbo].[sample]') IS NOT NULL
DROP TABLE [dbo].[sample]
GO
CREATE TABLE [dbo].[sample] (
[sno] INT IDENTITY(1,1) NOT NULL,
[student_no] INT NULL,
[head] INT NULL,
[task] VARCHAR(50) NULL)
GO
CREATE TRIGGER TR_Sample ON [dbo].[sample]
FOR UPDATE
AS
BEGIN
INSERT INTO SomeAuditTable(TableName,PrimaryKey,ListOfChanges)
SELECT '[dbo].[sample]' AS TableName,
'[sno]' As PrimaryKey,
CASE
WHEN INSERTED.[student_no] <> DELETED.[student_no]
THEN '[student_no]:OldValue' + CONVERT(VARCHAR,INSERTED.[student_no]) +'NewValue:' + CONVERT(VARCHAR,DELETED.[student_no] )
ELSE ''
END
+
CASE
WHEN INSERTED.[head] <> DELETED.[head]
THEN '[head]:OldValue' + CONVERT(VARCHAR,INSERTED.[head]) +'NewValue:' + CONVERT(VARCHAR,DELETED.[head] )
ELSE ''
END
+
CASE
WHEN INSERTED.[task] <> DELETED.[task]
THEN '[task]:OldValue' + CONVERT(VARCHAR,INSERTED.[task]) +'NewValue:' + CONVERT(VARCHAR,DELETED.[task] )
ELSE ''
END
FROM INSERTED
INNER JOIN DELETED ON INSERTED.[sno] = DELETED.[sno]
END --TRIGGER
Lowell
January 1, 2014 at 2:04 pm
navimir (12/5/2013)
Could you plz let me know how to check in trigger for old value and new value so that the result can be added to audit table??
This is a "field level" audit table and, if you truly have 100's of tables that you're going to audit in such a fashion, that table will quickly grow into a MONSTER table that will need a huge amount of help in the very near future. Your backups will become impossibly long, your restores will take even longer, and your nightly maintenance will begin to take so long that you won't have time for it all.
Before you instantiate such an audit system, you need to do a deep dive on Partitioned Tables (Enterprise Edition) or Partitioned Views (Standard Edition or Enterprise Edition). Both have a huge number of caveats and it'll take a bit to get your arms around it all, including the monthly switchovers, but it will be well worth it in the long run.
You also need to be advised that whatever triggers you make, do NOT make a generic trigger, especially if it's a CLR, because such triggers require something to be dynamic and both the INSERTED and DELETED tables go out of scope for any dynamic lookups. That means that the trigger must make a copy of those tables and, since I just got done eliminating all such generic triggers from my bread'n'butter database, you have to trust me when I say that generic triggers (one designed to work on any table via copy'n'paste) are incredibly slow and resource intensive. You need to write a stored procedure that will generate proper T_SQL to create a trigger for the table name you give it. You also need to be advised that there are certain datatypes (mostly blobs but there are others) that CANNOT be audited by triggers alone.
Also be advised that if a table doesn't have a PK or at least a UNIQUE index on it, auditing is a bit like painting with no paint on your brush.
Last but not least, I strongly recommend that you look into the SQL_VARIANT as the datatype for your "old" and "new" columns in the audit table.
Done correctly, audit tables can be a dream but it actually takes a fair bit to do them correctly especially for as many tables as what you want to audit. The creation of an audit system is NOT a trivial task to do correctly and, if you do it incorrectly, it will become a server eating monster in nearly all aspects.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply