Solved

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

Posted on 2013-11-15
5
3,787 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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