DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use instead of trigger to comply with Sorbanes-Oxley?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to create trigger protection for the Audit Trail table against unauthorized updates and deletes.

-- SQL Server instead of update and delete trigger

-- SQL instead of trigger - SQL audit log table

USE tempdb;

 

CREATE TABLE Audit (

  AuditID     INT    IDENTITY    PRIMARY KEY,

  EventTime   DATETIME    DEFAULT (getdate()),

  DESCRIPTION VARCHAR(512))

 

GO

 

INSERT INTO Audit

           (DESCRIPTION)

VALUES     ('General Ledger process completed OK')

 

INSERT INTO Audit

           (DESCRIPTION)

VALUES     ('Accounts Payable night job failed')

 

INSERT INTO Audit

           (DESCRIPTION)

VALUES     ('Inventory daily processing succeeded')

 

INSERT INTO Audit

           (DESCRIPTION)

VALUES     ('Accounts Receivables weekly report succeeded')

 

GO

 

CREATE TRIGGER trigAuditGuard

ON Audit

INSTEAD OF UPDATE,DELETE

AS

  BEGIN

    INSERT INTO Audit

               (DESCRIPTION)

    SELECT 'Direct audit update, delete denied to >' + USER + '< for AuditID ' + convert(VARCHAR,AuditID) + ' New: ' + DESCRIPTION

    FROM   Inserted

    

    INSERT INTO Audit

               (DESCRIPTION)

    SELECT 'Direct audit update, delete denied to >' + USER + '< for AuditID ' + convert(VARCHAR,AuditID) + ' Old: ' + DESCRIPTION

    FROM   Deleted

  END

 

GO

 

UPDATE Audit

SET    DESCRIPTION = 'Accounts Payable night job succeeded'

WHERE  AuditID = 2

 

GO

 

DELETE FROM Audit

WHERE       AuditID = 4

 

GO

 

SELECT *

FROM   Audit

 

GO

 

/* Results

 

AuditID     EventTime                     Description

1           2019-02-21 14:57:15.233  General Ledger process completed OK

2           2019-02-21 14:57:15.233  Accounts Payable night job failed

3           2019-02-21 14:57:15.233  Inventory daily processing succeeded

4           2019-02-21 14:57:15.233  Accounts Receivables weekly report succeeded

5           2019-02-21 14:57:15.280  Direct audit update, delete denied to >dbo< for AuditID 2 New: Accounts Payable night job succeeded

6           2019-02-21 14:57:15.280  Direct audit update, delete denied to >dbo< for AuditID 2 Old: Accounts Payable night job failed

7           2019-02-21 14:57:15.280  Direct audit update, delete denied to >dbo< for AuditID 4 Old: Accounts Receivables weekly report succeeded

*/

 

-- Cleanup

DROP TABLE tempdb.dbo.Audit

GO

Related article:

Kalen Delaney: INSTEAD OF Triggers Keep data changes in check with this new trigger type

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE