InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 09:47:49 GMT20http://www.sqlservercentral.com/Forums/Topic1422700-1550-1.aspxOh well. Here is the code. Sorry for putting it here....USE masterGOCREATE DATABASE AuditTestGOUSE AuditTestGOCREATE TABLE Customer( CustID int IDENTITY NOT NULL CONSTRAINT PK_Customer_On_CustID PRIMARY KEY CLUSTERED, CustFName varchar(20) NULL, CustMName varchar(35) NULL, CustLName varchar(35) NULL, CurrRec bit NOT NULL DEFAULT (1), LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(), LCUser varchar(50) NOT NULL DEFAULT USER, LCDate datetime NOT NULL DEFAULT GETDATE())GOCREATE TABLE CustomerAudit( CustAuditID int IDENTITY NOT NULL CONSTRAINT PK_CustomerAudit_On_CustAuditID PRIMARY KEY CLUSTERED, CustID int NOT NULL, CustFName varchar(20) NULL, CustMName varchar(35) NULL, CustLName varchar(35) NULL, CurrRec bit NOT NULL DEFAULT (1), LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(), LCUser varchar(50) NOT NULL DEFAULT USER, LCDate datetime NOT NULL DEFAULT GETDATE(), FinalDelete bit NOT NULL DEFAULT (0))GO/* ============================================================================================== *//* = System Tables = *//* ============================================================================================== */CREATE TABLE AppErrorLog( EID int IDENTITY NOT NULL CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED, EDate datetime NOT NULL, EUser varchar(50) NULL, EMessage varchar(1000) NULL, ESource varchar(150) NOT NULL, ENo int NOT NULL DEFAULT 0, ESeverity int NOT NULL DEFAULT 0, ELineNo int NOT NULL DEFAULT 0, EHost varchar(50) NULL)GO/* ========================================================================================== */CREATE TABLE DBActivityLog( DBActivityID int IDENTITY NOT NULL CONSTRAINT PK_DBActivityLog_On_DBActivityID PRIMARY KEY CLUSTERED, DBASPROC varchar(300) NULL, DBAMessage varchar(1000) NULL, DBAAppName varchar(150) NULL DEFAULT APP_NAME(), DBAHost varchar(50) NULL DEFAULT HOST_NAME(), DBAUser varchar(50) NULL DEFAULT USER, DBADate datetime NULL DEFAULT GETDATE())GO/* ======================================================================================== */CREATE TABLE MiscValues( MiscID int NOT NULL CONSTRAINT PK_MiscValues_On_MiscID PRIMARY KEY CLUSTERED, MiscDesc varchar(100) NULL, MiscValue varchar(200) NULL, LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(), LCUser varchar(50) NOT NULL DEFAULT USER, LCDate datetime NOT NULL DEFAULT GETDATE())GO/* ======================================================================================== */CREATE TABLE zSys_AuditTable( AuditTableID int IDENTITY NOT NULL CONSTRAINT PK_zSys_AuditTable_On_AuditTableID PRIMARY KEY CLUSTERED, SchemaName varchar(128) NOT NULL, TableName varchar(128) NOT NULL, AuditThisTable bit NOT NULL DEFAULT (0), LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(), LCUser varchar(50) NOT NULL DEFAULT USER, LCDate datetime NOT 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 LCDateFROM 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_AuditTableSET AuditThisTable=0WHERE 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@custid int,@spstat int OUTPUT,@errmsg varchar(200) OUTPUT,@recn int OUTPUTASDECLARE @numrecs intDECLARE @mvalue varchar(200) --log db activityDECLARE @pvalue varchar(1000) --log db activityDECLARE @dbactivityidadded int --id value of record inserted into DBActivityLog, if turned onDECLARE @continueproc bit --continue processingSET NOCOUNT ONSET @spstat = 1 -- go ahead and set to okSET @errmsg = '' -- go ahead and set to okSET @recn = 0 -- go ahead and set to okSET @dbactivityidadded = 0 -- set to 0SET @continueproc = 1 -- set to okBEGIN 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 @spstatEND TRYBEGIN CATCH DECLARE @ErrorNo int, @Severity int, @State int, @LineNo int, @errmessage varchar(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 CATCHGO/* ====================================================================================== *//* Create Trigger for table Customer to handle Last Change and/or Audit */CREATE TRIGGER dbo.TG_Trigger_On_Table_CustomerON dbo.CustomerFOR INSERT, UPDATE, DELETEASBEGIN TRY DECLARE @idval int DECLARE @CountDel int DECLARE @CountIn int DECLARE @logauditactivity bit DECLARE @mvalue varchar(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 ENDEND TRYBEGIN CATCH DECLARE @ErrorNo int, @Severity int, @State int, @LineNo int, @errmessage varchar(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 CATCHGO/* 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 CustomerUPDATE Customer SET CustLName = 'Smith' WHERE CustID = 1SELECT * FROM CustomerSELECT * FROM CustomerAuditUPDATE Customer SET CustLName = 'Johnson' WHERE CustID = 1SELECT * FROM CustomerSELECT * FROM CustomerAuditDECLARE @st intDECLARE @rn intDECLARE @em varchar(200)EXEC dbo.usp_Read_Customer_All_Audit_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUTSELECT @st as Status, @em as ErrorMessageDELETE FROM Customer WHERE CustID = 1DECLARE @st intDECLARE @rn intDECLARE @em varchar(200)EXEC dbo.usp_Read_Customer_All_Audit_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUTSELECT @st as Status, @em as ErrorMessageSELECT * FROM DBActivityLogSELECT * FROM AppErrorLogTue, 12 Mar 2013 12:05:37 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic1422700-1550-1.aspxI 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).Tue, 12 Mar 2013 12:02:10 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic1422700-1550-1.aspxImagine 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.aspxRegards,Mon, 11 Mar 2013 07:29:44 GMTandrecesarrhttp://www.sqlservercentral.com/Forums/Topic1422700-1550-1.aspxDear Experts How to use triggers in auditing and in which casesCan any one provide examplesThanksThu, 21 Feb 2013 10:19:41 GMTzi