Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-15
5
Medium Priority
?
4,146 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 1500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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