Audit triggers

  • Dear Experts

    How to use triggers in auditing and in which cases

    Can any one provide examples

    Thanks

  • Imagine that you want to know all inserts, delete's and updates that have been made into an specific table in certain period of time, or because a crash, or because a bad data being processed, so, for these and others situations you can use the triggers to tracking the changes. This can be very helpful to know if there is some malicious user changing things in you database tables.

    Have a look at this topic:

    http://www.sqlservercentral.com/Forums/Topic1429035-1550-1.aspx

    Regards,

    André CR
  • I have attached examples of how I do them. We use them for accountability, and for troubleshooting ("why is it THIS value???"). I have a mechanism embedded that allows you to turn on and off auditing by table. We use this method for any internally-developed databases. They are used by only a few in each department, so it won't be overwhelming the database to do it this way.

    If you are doing hundreds or thousands of transactions a minute, then I would look for a different way of doing it.

    Hope it helps (and I hope my attachments attached).

  • Oh well. Here is the code. Sorry for putting it here....

    USE master

    GO

    CREATE DATABASE AuditTest

    GO

    USE AuditTest

    GO

    CREATE TABLE Customer

    (CustID int IDENTITYNOT NULL

    CONSTRAINT PK_Customer_On_CustID PRIMARY KEY CLUSTERED,

    CustFNamevarchar(20)NULL,

    CustMNamevarchar(35)NULL,

    CustLNamevarchar(35)NULL,

    CurrRecbitNOT NULLDEFAULT (1),

    LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),

    LCUservarchar(50)NOT NULLDEFAULT USER,

    LCDatedatetime NOT NULLDEFAULT GETDATE()

    )

    GO

    CREATE TABLE CustomerAudit

    (CustAuditID int IDENTITYNOT NULL

    CONSTRAINT PK_CustomerAudit_On_CustAuditID PRIMARY KEY CLUSTERED,

    CustIDintNOT NULL,

    CustFNamevarchar(20)NULL,

    CustMNamevarchar(35)NULL,

    CustLNamevarchar(35)NULL,

    CurrRecbitNOT NULLDEFAULT (1),

    LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),

    LCUservarchar(50)NOT NULLDEFAULT USER,

    LCDatedatetime NOT NULLDEFAULT GETDATE(),

    FinalDeletebitNOT NULLDEFAULT (0)

    )

    GO

    /* ============================================================================================== */

    /* = System Tables = */

    /* ============================================================================================== */

    CREATE TABLE AppErrorLog

    (EID int IDENTITY NOT NULL

    CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED,

    EDatedatetimeNOT NULL,

    EUservarchar(50)NULL,

    EMessagevarchar(1000)NULL,

    ESource varchar(150)NOT NULL,

    ENointNOT NULLDEFAULT 0,

    ESeverityintNOT NULLDEFAULT 0,

    ELineNo intNOT NULLDEFAULT 0,

    EHost varchar(50)NULL

    )

    GO

    /* ========================================================================================== */

    CREATE TABLE DBActivityLog

    (DBActivityID int IDENTITY NOT NULL

    CONSTRAINT PK_DBActivityLog_On_DBActivityID PRIMARY KEY CLUSTERED,

    DBASPROCvarchar(300)NULL,

    DBAMessagevarchar(1000)NULL,

    DBAAppNamevarchar(150)NULLDEFAULT APP_NAME(),

    DBAHost varchar(50)NULLDEFAULT HOST_NAME(),

    DBAUservarchar(50)NULLDEFAULT USER,

    DBADatedatetimeNULLDEFAULT GETDATE()

    )

    GO

    /* ======================================================================================== */

    CREATE TABLE MiscValues

    (MiscID intNOT NULL

    CONSTRAINT PK_MiscValues_On_MiscID PRIMARY KEY CLUSTERED,

    MiscDescvarchar(100)NULL,

    MiscValuevarchar(200)NULL,

    LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),

    LCUservarchar(50)NOT NULL DEFAULT USER,

    LCDatedatetimeNOT NULL DEFAULT GETDATE()

    )

    GO

    /* ======================================================================================== */

    CREATE TABLE zSys_AuditTable

    (AuditTableID int IDENTITYNOT NULL

    CONSTRAINT PK_zSys_AuditTable_On_AuditTableID PRIMARY KEY CLUSTERED,

    SchemaNamevarchar(128)NOT NULL,

    TableNamevarchar(128)NOT NULL,

    AuditThisTablebitNOT NULLDEFAULT (0),

    LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),

    LCUservarchar(50)NOT NULL DEFAULT USER,

    LCDatedatetimeNOT NULL DEFAULT GETDATE()

    )

    GO

    --Load all tables in the zSys_AuditTable table (determines dynamically which ones should be audited).

    INSERT INTO zSys_AuditTable

    SELECT CONVERT(varchar(128),TABLE_SCHEMA) AS SchemaName, CONVERT(varchar(128),TABLE_NAME) AS TableName, 1 AS AuditThisTable, HOST_NAME() AS LCHost, USER as LCUser, GETDATE() AS LCDate

    FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' AND TABLE_TYPE<>'VIEW' AND TABLE_NAME <> 'zSys_AuditTable' AND (TABLE_NAME NOT LIKE '%Audit')

    ORDER BY TABLE_NAME

    --Reset these as unaudited.

    UPDATE zSys_AuditTable

    SET AuditThisTable=0

    WHERE SchemaName='dbo' AND TableName IN ('AppErrorLog','DBActivityLog','MiscValues')

    INSERT INTO MiscValues VALUES (1,'Group Name','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (2,'Dept Name','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (3,'Addr1','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (4,'Addr2','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (5,'City','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (6,'ST','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (7,'Zip','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (8,'Phone','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (9,'Fax','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (10,'Manager','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (11,'Application Name','AuditExample',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (12,'ApplicationVersion','1.0',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (13,'ApplicationDescription','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (14,'DatabaseVersion','1.0',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (15,'DatabaseDescription','',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (16,'SMTPServerName','mail.mycompany.com',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (17,'DBMail Profile Name','SQL Server Agent Mail Profile',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (18,'ReplyEmailAddr','noreply@myemail.com',HOST_NAME(),USER,GETDATE())

    INSERT INTO MiscValues VALUES (19,'LogDBActivity','TRUE',HOST_NAME(),USER,GETDATE())-- log database activity (running of SPROCs, with their associated parameter values)

    INSERT INTO MiscValues VALUES (20,'LogAuditActivity','TRUE',HOST_NAME(),USER,GETDATE())-- log audit activity (record value changes sent to the Audit table)

    CREATE PROCEDURE dbo.usp_Read_Customer_All_Audit_Rec

    @custidint,

    @spstatintOUTPUT,

    @errmsgvarchar(200)OUTPUT,

    @recnintOUTPUT

    AS

    DECLARE @numrecs int

    DECLARE @mvaluevarchar(200) --log db activity

    DECLARE @pvalue varchar(1000) --log db activity

    DECLARE @dbactivityidadded int --id value of record inserted into DBActivityLog, if turned on

    DECLARE @continueproc bit --continue processing

    SET NOCOUNT ON

    SET @spstat = 1 -- go ahead and set to ok

    SET @errmsg = '' -- go ahead and set to ok

    SET @recn = 0 -- go ahead and set to ok

    SET @dbactivityidadded = 0 -- set to 0

    SET @continueproc = 1 -- set to ok

    BEGIN TRY

    --log db activity

    SET @pvalue = ''

    SET @pvalue = CONVERT(varchar(100),@custid)

    SELECT @mvalue=ISNULL(MiscValue,'') FROM MiscValues WHERE MiscDesc = 'LogDBActivity'

    if @mvalue = 'TRUE'

    BEGIN

    INSERT INTO DBActivityLog

    VALUES ('dbo.usp_Read_Customer_All_Audit_Rec','SPROC call - Params= ' + @pvalue,APP_NAME(),HOST_NAME(),USER,GETDATE())

    SET @dbactivityidadded = scope_identity()

    END

    if @continueproc = 1 --only continue if everything is ok

    BEGIN

    SELECT 0 AS CustAuditID,CustID,CustFName,CustMName,CustLName,CurrRec,LCHost,LCUser,LCDate, 0 AS FinalDelete

    FROM Customer (NOLOCK)

    WHERE CustID=@custid

    UNION ALL

    SELECT CustAuditID,CustID,CustFName,CustMName,CustLName,CurrRec,LCHost,LCUser,LCDate, FinalDelete

    FROM CustomerAudit (NOLOCK)

    WHERE CustID=@custid

    ORDER BY LCDate DESC

    SET @numrecs = @@rowcount

    if @numrecs=0

    BEGIN

    SET @spstat = -1

    SET @errmsg = 'No record selected'

    SET @recn = 0

    END

    END

    else

    BEGIN

    SET @errmsg = 'Encountered unknown error.'

    INSERT INTO AppErrorLog

    VALUES (GETDATE(), USER, @errmsg, 'dbo.usp_Read_Customer_All_Audit_Rec', 0, 0, 0, HOST_NAME())

    END

    RETURN @spstat

    END TRY

    BEGIN CATCH

    DECLARE@ErrorNoint,

    @Severityint,

    @Stateint,

    @LineNoint,

    @errmessagevarchar(1000)

    SELECT@ErrorNo = ERROR_NUMBER(),

    @Severity = ERROR_SEVERITY(),

    @State = ERROR_STATE(),

    @LineNo = ERROR_LINE(),

    @errmessage = ERROR_MESSAGE()

    SET @errmsg = CONVERT(varchar(200), @errmessage)

    SET @spstat = 0

    INSERT INTO AppErrorLog

    VALUES (GETDATE(), USER, @errmessage, 'dbo.usp_Read_Customer_All_Audit_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())

    END CATCH

    GO

    /* ====================================================================================== */

    /* Create Trigger for table Customer to handle Last Change and/or Audit */

    CREATE TRIGGER dbo.TG_Trigger_On_Table_Customer

    ON dbo.Customer

    FOR INSERT, UPDATE, DELETE

    AS

    BEGIN TRY

    DECLARE @idvalint

    DECLARE @CountDelint

    DECLARE @CountInint

    DECLARE @logauditactivitybit

    DECLARE @mvaluevarchar(200)

    SELECT @CountDel = COUNT(*) FROM Deleted

    SELECT @CountIn = COUNT(*) FROM Inserted

    -- Do check to see if need to audit this table

    SET @logauditactivity = 0

    SELECT @mvalue=ISNULL(MiscValue,'') FROM MiscValues WHERE MiscDesc = 'LogAuditActivity'

    if @mvalue = 'TRUE' -- only if even turned on do we check for the individual table

    BEGIN

    if EXISTS(SELECT * FROM zSys_AuditTable WHERE SchemaName='dbo' AND TableName='Customer' AND AuditThisTable=1)

    SET @logauditactivity = 1 -- set to true

    else

    SET @logauditactivity = 0 -- set to false

    END

    if @CountDel = 0 and @CountIn >= 1-- inserted

    BEGIN

    BEGIN TRANSACTION

    UPDATE Customer

    SET LCDATE = GETDATE(),

    LCUSER = USER,

    LCHOST = HOST_NAME()

    FROM dbo.Customer s

    JOIN inserted i

    ON i.CustID = s.CustID

    COMMIT TRANSACTION

    END

    IF @CountDel >= 1 and @CountIn = 0 and @logauditactivity = 1-- deleted

    BEGIN

    BEGIN TRANSACTION

    INSERT INTO CustomerAudit

    SELECT CustID, CustFName, CustMName, CustLName, CurrRec, LCHost, LCUser, LCDate, 0 AS FinalDelete FROM DELETED

    INSERT INTO CustomerAudit

    SELECT CustID, CustFName, CustMName, CustLName, CurrRec, HOST_NAME() AS LCHost, USER AS LCUser, GETDATE() AS LCDate, 1 AS FinalDelete FROM DELETED

    COMMIT TRANSACTION

    END

    if @CountDel >= 1 and @CountIn >= 1-- updated

    BEGIN

    BEGIN TRANSACTION

    if @logauditactivity = 1

    BEGIN

    INSERT INTO CustomerAudit

    SELECT CustID, CustFName, CustMName, CustLName, CurrRec, LCHost, LCUser, LCDate, 0 AS FinalDelete FROM DELETED

    END

    UPDATE Customer

    SET LCDATE = GETDATE(),

    LCUSER = USER,

    LCHOST = HOST_NAME()

    FROM dbo.Customer s

    JOIN inserted i

    ON i.CustID = s.CustID

    COMMIT TRANSACTION

    END

    END TRY

    BEGIN CATCH

    DECLARE@ErrorNoint,

    @Severityint,

    @Stateint,

    @LineNoint,

    @errmessagevarchar(1000)

    SELECT@ErrorNo = ERROR_NUMBER(),

    @Severity = ERROR_SEVERITY(),

    @State = ERROR_STATE(),

    @LineNo = ERROR_LINE(),

    @errmessage = ERROR_MESSAGE()

    ROLLBACK TRAN

    INSERT INTO AppErrorLog

    VALUES (GETDATE(), USER, @errmessage, 'TG_Trigger_On_Table_Customer', @ErrorNo, @Severity, @LineNo, HOST_NAME())

    END CATCH

    GO

    /* Note: Run these one at a time to see what it is doing */

    INSERT INTO Customer VALUES ('John','Q','Public',1,HOST_NAME(),USER,GETDATE())

    SELECT * FROM Customer

    UPDATE Customer SET CustLName = 'Smith' WHERE CustID = 1

    SELECT * FROM Customer

    SELECT * FROM CustomerAudit

    UPDATE Customer SET CustLName = 'Johnson' WHERE CustID = 1

    SELECT * FROM Customer

    SELECT * FROM CustomerAudit

    DECLARE @st int

    DECLARE @rn int

    DECLARE @em varchar(200)

    EXEC dbo.usp_Read_Customer_All_Audit_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT

    SELECT @st as Status, @em as ErrorMessage

    DELETE FROM Customer WHERE CustID = 1

    DECLARE @st int

    DECLARE @rn int

    DECLARE @em varchar(200)

    EXEC dbo.usp_Read_Customer_All_Audit_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT

    SELECT @st as Status, @em as ErrorMessage

    SELECT * FROM DBActivityLog

    SELECT * FROM AppErrorLog

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply