February 21, 2013 at 10:19 am
Dear Experts
How to use triggers in auditing and in which cases
Can any one provide examples
Thanks
March 11, 2013 at 7:29 am
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,
March 12, 2013 at 12:02 pm
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).
March 12, 2013 at 12:05 pm
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