Comparing Data in a Trigger
-
Hello Everyone,
Is there anyway that I can compare the same field data in a trigger. I need a way to keep track of before and after of the updated data. I hope this makes sense
CREATE TRIGGER [Log] ON [dbo].[WPPI_Main_Load_Globals]
FOR UPDATE
AS
DECLARE @msg varchar(4500), @newfield varchar(2000), @oldfield varchar(2000)
IF UPDATE(prefix)
BEGIN
SELECT @newfield=i.prefix, @oldfield=c.prefix
FROM inserted i
INNER JOIN dbo.WPPI_Main_Load_Globals c ON i.ODBC = c.ODBC
SET @msg = SYSTEM_USER + ' ' + CAST(GETDATE() as varchar(20)) + ' New Prefix: ' + @newfield + ' Old Prefix: ' + @oldfield
END
Thanks,
Ken
-
SELECT @newfield=i.prefix, @oldfield=c.prefix
FROM inserted i
INNER JOIN dbo.WPPI_Main_Load_Globals c ON i.ODBC = c.ODBC
change it to
SELECT @newfield=i.prefix, @oldfield=d.prefix
FROM inserted i
INNER JOIN deleted d ON i.ODBC = d.ODBC
-- I am assuming ODBC is pk
BTW that only works if only one row is affected at a time
* Noel -
Thanks, noeld that works perfectly. I assume how it works is, that because it is an update that both the inserted and deleted "objects" are available.
-
What are doing with @msg after it is set? Because as noel said what you are doing a=only works for single row updates. IF you are logging the action in a table you could do:
insert into log_table
(
action
)
Select
SYSTEM_USER + ' ' + CAST(GETDATE() as varchar(20)) + ' New Prefix: ' + I.prefix+ ' Old Prefix: ' + D.prefix
From
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack,
For the most part this table is read only. The data used in the table is used to load a DTS package Globals at runtime for execution. In the past few weeks I've notice that some of the data in this table get change, inturn causing the DTS package to fail. I need to know who is changing it!
Ken
-
I thought something like that might be te case so having an audit table that you are inserting into can really give you a history of what is going on and allow you to identify patterns. I would even break each peice into a separate column. changed_by, date_changed, old_val, new_val. Then you can query the table to determine what is going on and by whom. I don't know how the data is being altered, but if it is being done using SQL you could have multiple rows affected and you would only be logging one with the solution you currently have in place.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply