March 23, 2004 at 9:11 am
Looking at the inserted and deleted tables using a trigger, I need to write to a transactions table only the columns that have been changed on the update action. The table consists of 24 columns. What is the easiest way to accomplish this?
March 23, 2004 at 1:08 pm
Use the COLUMNS_UPDATED() function. Be careful, this function will tell you what was updated , not necessarily what changed. If col2 was = 1 and I write an update statement to set col2 = 1, COLUMNS_UPDATED() will report that col2 was updated. You may need to compare before and after images to find out what changed. I can give you a sample trigger using this if you want.
Francis
March 23, 2004 at 8:25 pm
If you have a sample trigger that compares before and after images of the inserted and deleted table columns that would be great. I only need to write to the transaction table the changed information and there are 24 columns that need to be checked for changes.
March 24, 2004 at 6:26 am
This is what I have right now in my trigger. I am getting error messages when I try to compare If i.ssn <> d.ssn, what I want to do is find out if there is a difference or there was some modification and write the new value to the transaction table.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER Part_Trigger ON [dbo].[Log_tblparticipant]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @ins int, @del int, @type varchar(30), @colpos int,
@PLANID VARCHAR(6), @SSN VARCHAR(9), @TITLE VARCHAR(50),
@FNAME VARCHAR(50), @MNAME VARCHAR(50), @LNAME VARCHAR(50),
@address1 varchar(50), @address2 varchar(50), @city varchar(50),
@state varchar(2), @zip varchar(10), @maritalstatus varchar(2),
@dobirth datetime, @dotermntn datetime, @doparticiptn datetime,
@dohire datetime, @dorehire datetime, @email varchar (50), @category varchar(50),
@salary decimal(18,2), @deferralperc decimal (18,0), @status varchar(10), @cumulativehours decimal (18,2)
select @ins = count(*) from inserted
select @del = count(*) from deleted
if @ins = 0 AND @del = 0 return -- nothing updated/deleted
if @del = 0 set @type = 'insert' else set @type = 'update'
if @type = 'update'
begin
-- perform action for update
SELECT d.planid, d.SSN, d.title, d.fname, d.mname, d.lname, d.address1, d.address2, d.city, d.state, d.zip, d.maritalstatus, d.dobirth, d.dotermntn, d.doparticiptn, d.dohire, d.dorehire, d.category, d.email, d.salary, d.deferralperc, d.status, d.cumulativehours,
i.planid, i.SSN, i.title, i.fname, i.mname, i.lname, i.address1, i.address2, i.city, i.state, i.zip, i.maritalstatus, i.dobirth, i.dotermntn, i.doparticiptn, i.dohire, i.dorehire, i.category, i.email, i.salary, i.deferralperc, i.status, i.cumulativehours
FROM deleted d, inserted i
where d.ssn = i.ssn
if d.ssn <> i.ssn
set @ssn = i.ssn
end
else
begin
-- perform action for insert
INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, address1, address2, city, state, zip, maritalstatus, dobirth, dotermntn, doparticiptn, dohire, dorehire, category, email, salary, deferralperc, status, cumulativehours)
SELECT ins.planid, ins.SSN, ins.title, ins.fname, ins.mname, ins.lname, ins.address1, ins.address2, ins.city, ins.state, ins.zip, ins.maritalstatus, ins.dobirth, ins.dotermntn, ins.doparticiptn, ins.dohire, ins.dorehire, ins.category, ins.email, ins.salary, ins.deferralperc, ins.status, ins.cumulativehours
FROM inserted ins
end
end
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 24, 2004 at 6:39 am
I'm not expert, but this sounds like a use for the Instead Of option on a trigger (SQL 2000). If your Instead Of trigger catches the update, you can check each value to see if it is different from the one in the database and update if it is.
Someone who knows more about this than I can check my logic because I have never actually done this.
I will be interested to hear the final solution for this as I have a place where it may be useful also.
Chris
March 24, 2004 at 6:44 am
The instead of trigger would not update the original table tbl_participant and this needs to happen. The logx_tblparticipant is a table where I am capturing the modifications made to the tbl_participant table in order to update another systems files with the changes.
March 24, 2004 at 8:03 am
First off, looking at your code, you have an insert update trigger so if it fires there will ALWAYS be data in the inserted table, so getting a row count on that is unnecessary. You can determine the type just by determining if there is data in the deleted table and the If exists syntax is recommended over doing a Count(*).
Second, do you want to store the entire record as you are with the insert portion of your trigger or do you just want to store the value (old or new) of the column(s) that changed with the rest as Null?
If you are storing the entire record I would store both the before and after records. This is a simple way to track changes. Add transaction type and a transaction time time to your log table and do the following:
Set @transaction_date = getdate()
Insert Into logtable
Select
columns
'Before' as transaction_type,
@transaction_date
From
inserted
Insert Into logtable
Select
columns
'After' as transaction_type,
@transaction_date
From
deleted
If you only want to store the changed fields with the rest Null you could do this:
Insert Into Logtable
Select
Case when I.column = D.column Then Null
Else I.column (or D.column)
From
inserted I Join
deleted D On
I.pk = D.pk
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
March 24, 2004 at 11:07 am
Thanks. I decided to write both the before and after records. It is working and I am happy. I tried to change to if exists but I was getting errors.
This is the code that works.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- This trigger is set off by an insert (enrollment) or an
-- update (indicative data changes) on the tblparticipant table
-- The record keeping system needs to be updated with these changes.
-- A transaction history table (LOGX_tblparticipant)will be used for this purpose.
-- Enrollments will be transaction type 'Enroll
-- Updates will create two transaction types 'Before' the data before the change
-- and 'After' the new updates. There are also two additional fields that are updated
-- with each transaction, Username and DateChanged (includes the date and time of change.
ALTER TRIGGER Part_Trigger ON [dbo].[Log_tblparticipant]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @ins int, @del int, @type varchar(30), @transaction_type varchar(6)
select @ins = count(*) from inserted
select @del = count(*) from deleted
if @ins = 0 AND @del = 0 return -- nothing updated/deleted
if @del = 0 set @type = 'insert' else set @type = 'update'
-- On an update there are before and after images on the systems tables called deleted and inserted.
-- We are going to write this information to our LOGX_tblparticipant for audit purposes.
if @type = 'update'
-- perform action for update
begin
INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, address1, address2, city, state, zip, maritalstatus, dobirth, dotermntn, doparticiptn, dohire, dorehire, category, email, salary, deferralperc, status, cumulativehours, transaction_type)
SELECT i.planid, i.SSN, i.title, i.fname, i.mname, i.lname, i.address1, i.address2, i.city, i.state, i.zip, i.maritalstatus, i.dobirth, i.dotermntn, i.doparticiptn, i.dohire, i.dorehire, i.category, i.email, i.salary, i.deferralperc, i.status, i.cumulativehours, 'After'
FROM inserted i
INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, address1, address2, city, state, zip, maritalstatus, dobirth, dotermntn, doparticiptn, dohire, dorehire, category, email, salary, deferralperc, status, cumulativehours, transaction_type)
SELECT d.planid, d.SSN, d.title, d.fname, d.mname, d.lname, d.address1, d.address2, d.city, d.state, d.zip, d.maritalstatus, d.dobirth, d.dotermntn, d.doparticiptn, d.dohire, d.dorehire, d.category, d.email, d.salary, d.deferralperc, d.status, d.cumulativehours, 'Before'
FROM deleted d
end
-- perform action for insert this is a new enrollment
else
begin
INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, address1, address2, city, state, zip, maritalstatus, dobirth, dotermntn, doparticiptn, dohire, dorehire, category, email, salary, deferralperc, status, cumulativehours, transaction_type)
SELECT i.planid, i.SSN, i.title, i.fname, i.mname, i.lname, i.address1, i.address2, i.city, i.state, i.zip, i.maritalstatus, i.dobirth, i.dotermntn, i.doparticiptn, i.dohire, i.dorehire, i.category, i.email, i.salary, i.deferralperc, i.status, i.cumulativehours, 'Enroll'
FROM inserted i
end
end
RETURN
March 24, 2004 at 11:23 am
I probably should have included the if exists in my sample. I would replace the COunt(*) code and the if @type = 'Update' to this:
If Exists(Select * from deleted) --updated record
Begin
Insert from inserted
Insert from deleted
End
Else -- new record inserted
Begin
Insert from inserted
End
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply