Solved

SQL server 2008 -how to Audit old/new columns value through after Insert/update /delete trigger

Posted on 2013-11-15
5
3,708 Views
Last Modified: 2013-11-17
Hi,
   
   Would some one please help by give me some codes example for Trigger syntax for SQL server 2008 .  The below are what I am looking for to do.
   ( I am not familiar with SQL server 2008 trigger,  I only know some basic of Oracle trigger).

For Audit report purpose, I would like to create the after insert, update, delete trigger that for every change made per record in  table A  ( For each insert row, update row, delete row ),  the trigger would able to record the change per row by insert into an audit table B.  Which Each row in  table B store the column value  before it is change and after it is changed   (2 column in table B one with old value and one with new value for a column that change per record).  

The reason is user want to tract every change made to Table A and able to use table B as audit report display through the app.  ( app build to display results set based on user input for tract change )

Thanks very much,

Mywoody
0
Comment
Question by:Mywoody
5 Comments
 
LVL 5

Accepted Solution

by:
Alok-Agarwal earned 500 total points
ID: 39652924
SQL Server 2008 now comes with built in databse audit specifications.

Please refer below URL for more details.

http://www.codeproject.com/Articles/166264/Audits-on-MS-SQL-Server-2008
0
 
LVL 5

Expert Comment

by:Alok-Agarwal
ID: 39652925
Following database level audit actions are possible in SQL Server 2008

SELECT =>This event is raised whenever a SELECT is issued.
UPDATE=>This event is raised whenever an UPDATE is issued.
INSERT=>This event is raised whenever an INSERT is issued.
DELETE =>This event is raised whenever a DELETE is issued.
EXECUTE =>This event is raised whenever an EXECUTE is issued.
RECEIVE=>This event is raised whenever a RECEIVE is issued.
REFERENCES=>This event is raised whenever a REFERENCES permission is checked
 
HTH
Thanks
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39652992
If you need your own trackChanges table then this will get you started

CREATE TRIGGER YourTrigger ON YourTable
   AFTER INSERT,UPDATE,DELETE
AS

DECLARE @HistoryType    char(1) --"I"=insert, "U"=update, "D"=delete
DECLARE @ActionDate DateTime
Set @ActionDate = GETDATE()

SET @HistoryType=NULL

IF EXISTS (SELECT * FROM INSERTED)
BEGIN
    IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        --UPDATE
        SET @HistoryType='U'
    END
    ELSE
    BEGIN
        --INSERT
        SET @HistoryType='I'
    END
    --handle insert or update data
    INSERT INTO YourLog
            (ActionType,ActionDate --(list your columns here)
        SELECT
            @HistoryType,@ActionDate --(list your columns here)
            FROM INSERTED

END
ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
    --DELETE
    SET @HistoryType='D'

    --handle delete data, insert into both the history and the log tables
    INSERT INTO YourLog
            (ActionType,ActionDate--(list your columns here)
        SELECT
            @HistoryType,@ActionDate--(list your columns here)
            FROM DELETED

END

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39653288
"Which Each row in  table B store the column value  before it is change and after it is changed   (2 column in table B one with old value and one with new value for a column that change per record).  "

What happens if you update more than one column in table A, lets say N? Will you have 2*N columns in B, like col_n_before and col_n after? It becomes too complicated.

It is simpler to insert every time whatever is in the INSERT and DELETED aux tables that are used for data manipulation. This way you will have the row before and after and you will see what values have changed.

INSERT INTO B 'Before' as stat, DELETED.*
INSERT INTO B 'After' as stat, INSERTED.*

Where B will have an extra column called, stat (can be whatever you want) and then the same columns as in A.
0
 

Author Closing Comment

by:Mywoody
ID: 39655026
Thank you
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 51
encyps queries mssql 15 27
Create calculation and case in query with times 13 13
Service Statictic 11 14
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now