Solved

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

Posted on 2013-11-15
5
3,938 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 27

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 79
SQL Procedure 7 49
SQL Lag Function DateDiff 2 26
Failover Cluster Primary Nodes Current Vote = 0 5 35
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

734 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