September 27, 2010 at 3:37 am
Hello Friends,
I need your help for enhancement in the TRIGGER which i have scripted for my organization.
Actually my database is not password protected and is opened for system administrators and programmers.
They regularly fire database updation/ insertion/ deletion. To keep the log of every update/ insert/ delete in important tables, i have made a TRIGGER which stores the log for every U/I/D.
Example: Output for the update query
UPDATE tbbtmstr set sht_nm='Ritesh Mehta' WHERE act_no='0002020203223')
gives stored output in below format.
Date |Table Name| Field Name|Old Value|New Value |Host Name|
---------------------------------------------------------------------------------------
1/1/1|tbbtmstr| sht_nm(U/I/D) |Ritesh|Ritesh Mehta| SYSPC1 |
I need enhancement in the output by storing the "WHERE" condition in the log.
(in above example: acc_no='0002020203223').
Can anybody advice me, how to do that?
Regards,
Ritesh Mehta
--SAMPLE TRIGGER Script--
CREATE TRIGGER trg_tbbtmstr ON tbbtmstr FOR update, delete AS
Declare @Type char(1)
declare @ovalue varchar(1000)
declare @ovalue1 varchar(1000)
declare @nvalue varchar(1000)
declare @nvalue1 varchar(1000)
declare @sql varchar(2000)
declare @bit int ,@field int ,@maxfield int ,@char int , @fieldname varchar(128) ,@TableName varchar(128)
SET NOCOUNT ON
if left(ltrim(app_name()),4) not in ('Comm','WinB','SysA','main','????') -- IF fired from known applications
BEGIN
select @TableName ='tbbtmstr'
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'
select * into t2 from inserted
select * into t1 from deleted
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tbbtmstr'
while @field < @maxfield
BEGIN
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if (substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 and left(app_name(),4) <> 'WinB%') or (@Type in ('I','D') and left(app_name(),4) <> 'WinB%') -- IF fired from known applications
BEGIN
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
if left(ltrim(app_name()),4) not in ('Comm','WinB','SysA','main','????')
BEGIN
set @ovalue = 'select ' + @fieldname + ' from t1'
create table #Data (varchar varchar(1000) )
insert #Data exec(@ovalue)
select @ovalue1= varchar from #Data
drop table #Data
set @nvalue = 'select ' + @fieldname + ' from t2'
create table #Data1 (varchar varchar(1000) )
insert #Data1 exec(@nvalue)
select @nvalue1= varchar from #Data1
drop table #Data1
INSERT INTO tbbtlogdata (datetime_changed,changed_by_host,byuser,FQuery,tablename, ovalue, nvalue)
(select GetDate(),host_name() , system_user ,app_name(), 'tbbtmstr: (' + @Type + ') : ' + @fieldname,
@ovalue1 , @nvalue1 )
END
END
END
drop table t1
drop table t2
END
September 29, 2010 at 5:02 am
Dear Friends,
Awaiting your valuable comments for my query.
Is it possible ?? :Whistling: Please give your feedback.
Regards,
Ritesh Mehta
September 29, 2010 at 5:29 am
you might not be able to do this in the trigger; you can get only the first 256 characters of the entire commnad...so a long command would get truncated in a trigger, and you could not see it all;
a server side trace is the recommended way to do this...you can put filters on it so only commands agaisnst the table(s) in question are captured.
as a proof of concept, here is a trigger that captures the command executed via dbcc inputbugger:
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
--used to capture the row id plus a bunch of audit information
CREATE TABLE [dbo].[WHATEVER_AUDIT] (
[WHATEVERID] INT NOT NULL,
[INSERTUPDATE] NVARCHAR(30) NULL,
[LASTCOMMAND] NVARCHAR(max) NULL,
[USER_NAME] NVARCHAR(256) NULL,
[SUSER_NAME] NVARCHAR(256) NULL,
[CURRENT_USER] NVARCHAR(256) NULL,
[SYSTEM_USER] NVARCHAR(256) NULL,
[SESSION_USER] NVARCHAR(256) NULL,
NVARCHAR(256) NULL,
[APPLICATION_NAME] NVARCHAR(256) NULL,
[HOST_NAME] NVARCHAR(256) NULL,
[OCCURANCE_DATE] DATETIME DEFAULT GETDATE() NOT NULL)
GO
CREATE TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @INSERTUPDATE NVARCHAR(30),
@LASTCOMMAND NVARCHAR(max)
--################################################################################################
--note these two methods do not get the last command when inside a trigger;
--included for complete solution
--get the last command by the current spid:
--DECLARE @handle varbinary(64)
--SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID
--SELECT @LASTCOMMAND = [Text] FROM ::fn_get_sql(@Handle)
--get the last command by the current spid:
--SELECT @LASTCOMMAND = DEST.TEXT
--FROM sys.[dm_exec_connections] SDEC
--CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
--WHERE SDEC.[most_recent_session_id] = @@SPID
--################################################################################################
--because dbcc inputbuffer is limited to 256 chars, you may need to combine this witha DML trace
--################################################################################################
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo
FROM @buffer
--assume it is an insert
SET @INSERTUPDATE='INSERT'
--if there's data ind eleted, it's an update
IF EXISTS(SELECT * FROM DELETED)
SET @INSERTUPDATE='UPDATE'
--insert data that meets the criteria: the column 'description' is null
INSERT INTO [WHATEVER_AUDIT]
SELECT
INSERTED.WHATEVERID,
@INSERTUPDATE,
@LASTCOMMAND,
user_name() AS [user_name],
suser_name() AS [suser_name],
current_user AS [current_user],
system_user AS [system_user],
session_user AS [session_user],
user AS ,
APP_NAME() AS [application_name],
HOST_NAME() AS [host_name],
getdate() AS [occurance_date]
FROM INSERTED
WHERE DESCRIP IS NULL
END --TRIGGER
GO
--does not trigger audit:
INSERT INTO WHATEVER(DESCRIP)
SELECT 'CANTALOUPE' UNION
SELECT 'TANGARINES' UNION
SELECT 'PLUMS' UNION
SELECT 'PEACHES' UNION
SELECT 'BLUEBERRIES'
--triggers one row out of multi row insert
INSERT INTO WHATEVER(DESCRIP)
SELECT NULL UNION
SELECT 'TANGARINES'
--triggers one row out of multi row insert
UPDATE WHATEVER SET DESCRIP = NULL WHERE WHATEVERID IN (4,5)
SELECT * FROM WHATEVER
SELECT * FROM [WHATEVER_AUDIT]
Lowell
September 29, 2010 at 10:52 pm
Hey Lowell,
Thanks dear. As per your suggestion with illustrated script, i have done changes in my script and it is working as per my requirement now. This will really help my organization to handle several issues.
Thanks again..
Regards,
Ritesh Mehta
September 30, 2010 at 3:46 am
Ritesh glad I could help, and thank you very much for the positive feedback. It's nice to know when something works well for someone.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply