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

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
MywoodyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Alok-AgarwalConnect With a Mentor Commented:
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
 
Alok-AgarwalCommented:
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
 
QuinnDexCommented:
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
 
ZberteocCommented:
"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
 
MywoodyAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.