August 1, 2008 at 3:14 pm
I have created a simple trigger that fires when a change is made to my work_order table. However, I'm having problems bringing other information into the script. For instance, I would like to see the client_net_address, and what data actually changed (oldvalue - newvalue). Can anyone point me in the right direction? Any help will be greatly appreciated.
Here is my current trigger:
CREATE TRIGGER WO_AUDIT
ON WORK_ORDER
FOR INSERT, UPDATE, DELETE AS
BEGIN
INSERT INTO ENV_AUDIT (BASE_ID, MODIFIED_DATE, USER_ID)
SELECT BASE_ID, GETDATE(), SUSER_SNAME()
FROM INSERTED
END
August 1, 2008 at 3:31 pm
I am providing you a sample Trigger that i guess will help you
CREATE TRIGGER [dbo].[utr_AUDIT_UPDATE_tablename]
ON [dbo].[tablename]
AFTER INSERT,UPDATE AS
DECLARE @userid varchar(20),
@AppID varchar(50),
@OldValue varchar(50),
@NewValue varchar(50),
@FieldName varchar(50)
-- Get UserID and ApplicationID.
SELECT @userid = RTRIM(sp.loginame),
@AppID = RTRIM(sp.program_name)
FROM Master..sysprocesses sp
WHERE spid=@@spid
-- Trace Updates to column.
IF UPDATE(columnname)
BEGIN
-- Set field name being captured.
SELECT @FieldName = 'columnname'
-- Get Old value.
SELECT @OldValue = columnname
FROM deleted
-- Get New value.
SELECT @NewValue = columnname
FROM inserted
-- Verify that there was a change.
IF ((ISNULL(@OldValue, 'Y') = 'Y') AND (ISNULL(@NewValue, 'Y') <> 'Y'))
OR ((ISNULL(@OldValue, 'Y') <> 'Y') AND (ISNULL(@NewValue, 'Y') = 'Y'))
OR (@OldValue <> @NewValue)
BEGIN
-- Save change.(all the change information is collected in AUDIT_Table)
INSERT INTO AUDIT_Table
(DatabaseName,
TableName,
FieldName,
OldValue,
NewValue,
UserID,
AppID)
Values (db_name(),
'tablename',
@FieldName,
@OldValue,
@NewValue,
@AppID)
END
END
Similary for all coulmns you need to audit ,above procedure Repeats.
Good Luck
August 1, 2008 at 3:40 pm
Do I need to pick a specific "columnname" for it to look at for changes?
Msg 207, Level 16, State 1, Procedure utr_AUDIT_UPDATE_WORK_ORDER, Line 17
Invalid column name 'columnname'.
Msg 207, Level 16, State 1, Procedure utr_AUDIT_UPDATE_WORK_ORDER, Line 23
Invalid column name 'columnname'.
Msg 207, Level 16, State 1, Procedure utr_AUDIT_UPDATE_WORK_ORDER, Line 27
Invalid column name 'columnname'.
August 4, 2008 at 10:30 am
Got it. Thanks.
August 4, 2008 at 12:00 pm
These two articles have a lot of information on logging and auditing:
http://qa.sqlservercentral.com/articles/Auditing/63247/
http://qa.sqlservercentral.com/articles/Auditing/63248/
Their discussions add a lot to them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2008 at 7:37 am
Nary, your example trigger is very flawed in that it doesn't handle multi-record actions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2008 at 10:04 am
I am just learning this stuff, but it seemed to do what we wanted. I did go back in and add the work order id and getdate. Any further help on improving this trigger would be greatly appreciated. I am a work in progress, but from what I here this is the place to learn it.
August 6, 2008 at 8:08 am
Take a look at the sample triggers in the articles and discussions I linked. Those are tested and proven.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2008 at 7:22 pm
You may want to version the table (see the link in my sig to get a document explaining the details). But that allows you to query the table to see the data as it existed at different times in the past. That might be more than what you need. If all you want to do is track changes and generate some before/after reports, just create a replica table with a timestamp column which will be part of the key.
-- Original table
create table MainTable(
PKcol ...,
Col1 ...,
Col2 ...,
Col3 ...,
constraint PK_MainTable primary key (PKcol)
);
create table TrackingTable(
PKcol ..., -- if this is Identity in MainTable, do NOT make it so here!
EffDate datetime not null,
Op char(1),
Col1 ...,
Col2 ...,
Col3 ...,
constraint PK_TrackingTable primary key (PKcol, EffDate)
);
Then the trigger on the main table table would just be this:create trigger T_Name on MainTable for Insert, Update, Delete
as begin
declare @Icount int, @Dcount int;
select @Icount = count(*) from Inserted;
select @Dcount = count(*) from Deleted;
if @Icount + @Dcount = 0
return;
if @Dcount = 0 begin
-- Insert operation
Insert TrackingTable(PKcol, EffDate, OP, ...)
select PKcol, GetDate(), 'I', ...
from Inserted;
end;
else if @ICount = 0 begin
-- Delete operation
Insert TrackingTable(PKcol, EffDate, OP)
select PKcol, GetDate(), 'D'
from Deleted;
end;
else begin
-- Update operation
Insert TrackingTable(PKcol, EffDate, OP, ...)
select PKcol, GetDate(), 'U', ...
from Inserted;
end;
end;
That way, your tracking table has a complete record of when an entity was created, every change made to it, and when it was deleted. You may, of course, add user info and other information you also want to track and omit the columns (if any) you don't need to track. This is a simplified versioning scheme but I think it gives you what you want.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 8, 2008 at 2:07 am
You may try this one out.
Here I used table (POS_Products) and table (Audit)
It may work out good for you as it caters for all columns changed;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [dbo].[tr_TriggerTest] on [dbo].[POS_Products] for insert, update, delete
as
declare
@Bit int,
@Field int,
@maxfield int,
@Char int,
@FieldName varchar(128),
@TableName varchar(128),
@PKCols varchar(100),
@SQL varchar(2000),
@UpdateDate varchar(21),
@UserName varchar(128),
@Type varchar(1),
@PKSelect varchar(100)
--Specify the name of the table to be audited
select @TableName = 'POS_Products'
--Gets the name of the user making changes to the table
select @UserName = system_user, @UpdateDate = convert(varchar(8),getdate(),112)
+ ' ' + convert(varchar(12), getdate(),114)
--Action
if exists(select * from inserted)
if exists(select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'
--Gets list of columns
select * into #ins from inserted
select * into #del from deleted
--Gets primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ' and', ' on')
+ ' i.'+ c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from information_schema.table_constraints pk, information_schema.key_column_usage c
where pk.table_name = @TableName
and constraint_type = 'PRIMARY KEY'
and c.table_name = pk.table_name
and c.constraint_name = pk.constraint_name
--Get Primary key for Insert
select @PKSelect = coalesce(@PKSelect + '+', '')
+ '''<' + column_name
+ '=''+ convert(varchar(100),
coalesce(i.'+ column_name +',d.' + column_name +')) + ''>'''
from information_schema.table_constraints pk,
information_schema.key_column_usage c
where pk.table_name = @TableName
and constraint_type = 'primary key'
and c.table_name = pk.table_name
and c.constraint_name = pk.constraint_name
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
select @field = 0,
@maxfield = max(Ordinal_Position)
from information_schema.columns
where table_name = @TableName
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 or @Type in('I', 'D')
begin
select @FieldName = column_name
from information_schema.columns
where table_name = @TableName
and Ordinal_Position = @field
select @sql = 'insert into Audit (Type, TableName, PK, FieldName, OldValue, NewValue,
UpdateDate, UserName)
values (select ''' + @Type +''','''
+ @TableName +''','+ @PKSelect
+ ','''+ @FieldName + ''''
+ ', convert(varchar(1000), d.' + @FieldName + ')'
+ ', convert(varchar(1000), i.' + @FieldName + ')'
+ ','''+ @UpdateDate + ''''
+ ','''+ @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + '<> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null))'
exec(@sql)
end
end
August 8, 2008 at 8:30 am
Thanks for all the help. It is greatly appreciated.
October 28, 2009 at 7:29 am
When I run this it adds the trigger, but once I go to change the address I get this error
A save operation on table 'RM_Customer_MSTR cannot find the table trying to add a trigger
What I'm wondering is if this is related to the INSERT INTO AUDIT_Table
Should this table already be there or does the code add it?
October 28, 2009 at 3:44 pm
I created this trigger and it populates the table with the correct data but it will not run the email portion at the bottom. Can someone tell me what i am doing wrong.
CREATE TRIGGER [dbo].[AUDIT_UPDATE_RM00101]
ON [dbo].[RM00101]
AFTER INSERT,UPDATE,DELETE AS
DECLARE @userid varchar(20),
@getdate-2 varchar(39),
@OldValue varchar(50),
@NewValue varchar(50),
@FieldName varchar(50),
@SQL varchar(1000),
@DBNAME varchar(10)
-- Get UserID and ApplicationID.
SELECT @userid = RTRIM(sp.loginame),
@getdate-2 = RTRIM(getdate())
FROM Master..sysprocesses sp
WHERE spid=@@spid
-- Trace Updates to column.
IF UPDATE(address1)
BEGIN
-- Set field name being captured.
SELECT @FieldName = 'Address1'
-- Get Old value.
SELECT @OldValue = address1
FROM deleted
-- Get New value.
SELECT @NewValue = address1
FROM inserted
-- Verify that there was a change.
IF ((ISNULL(@OldValue, 'Y') = 'Y') AND (ISNULL(@NewValue, 'Y') <> 'Y'))
OR ((ISNULL(@OldValue, 'Y') <> 'Y') AND (ISNULL(@NewValue, 'Y') = 'Y'))
OR (@OldValue <> @NewValue)
BEGIN
-- Save change.(all the change information is collected in AUDIT_Table)
/*CREATE TABLE AUDIT_Table
(Date_Changed varchar(39), FieldName VARCHAR(50), OldValue VARCHAR(50), NewValue VARCHAR(50),
UserID VARCHAR(20));*/
INSERT INTO AUDIT_Table
--TableName,
(FieldName,
OldValue,
NewValue,
UserID,
Date_Changed)
Values
(
@FieldName,
@OldValue,
@NewValue,
@getdate-2 --+ convert(varchar, getdate(), 10)
)
SET @DBNAME = 'Test5' --set this variable as the name of your database
SET @SQL = 'SELECT Date_Changed, FieldName, OldValue, UserID
FROM ' + @DBNAME + '.dbo.AUDIT_Table WHERE Date_Changed >= ' + char(39) +
convert(varchar(21), GetDate(), 101) + char(39)
EXEC master.dbo.xp_sendmail
@recipients = 'lcarrethers@dolese.com',
-- @copy_recipients = @COPY_RECIPIENTS,
-- @blind_copy_recipients = @BLIND_COPY_RECIPIENTS,
@subject = 'Address changed',
@message = "Attached is a list of address's that have been changed today",
@query = @SQL,
@attach_results = 'TRUE',
@width = 250
END
END
GO
October 29, 2009 at 9:06 am
what error do you get? Have you checked various logs?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 29, 2009 at 9:08 am
Oh, one more thing - be wary of using xp's in triggers. Better would be to put relevant data into a table that is asynchronously used to send the email if your process can allow for that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply