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

Triggers for insert,update,delete in MSSQL Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 6:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:56 AM
Points: 14, Visits: 64
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
Post #1520009
Posted Thursday, December 5, 2013 6:29 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 682, Visits: 1,226
what do you mean by output from the trigger. do you want to fill log table from a trigger?
Post #1520015
Posted Thursday, December 5, 2013 6:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:56 AM
Points: 14, Visits: 64
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????
Post #1520019
Posted Thursday, December 5, 2013 6:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:56 AM
Points: 14, Visits: 64
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
Post #1520022
Posted Thursday, December 5, 2013 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 12,903, Visits: 31,970
navimir (12/5/2013)
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


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520026
Posted Thursday, December 5, 2013 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:56 AM
Points: 14, Visits: 64
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??
Post #1520076
Posted Thursday, December 5, 2013 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 12,903, Visits: 31,970
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520148
Posted Wednesday, January 1, 2014 2:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse