RE: Audit triggers – SQLServerCentral

vikingDBA

SSCertifiable

Points: 5356

March 12, 2013 at 12:05 pm

#1596207

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

USE master

CREATE DATABASE AuditTest

USE AuditTest

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()

)

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)

)

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

/* = 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

)

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

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()

)

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

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()

)

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

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()

)

--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

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

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

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

/* 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

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

/* 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