Trigger to compare incoming record w/ previous record
-
I'm not sure this is possible, but I need to compare fields of an updated record with the previous version of the record, and then insert a history record in another table. Example:
table positions:
2345,jake,munson,boise,id
changes to
2345,jacob,munson,boise,id
In this example, I want a history record created in table positions_history only if fname or lname changes, but not if city or state changes.
Here is some code I've tried that doesn't work:
Create Trigger tbl_pts_positions_Update_01_Tr
On tbl_pts_positions
After Update As
If exists (
select p.poid
from tbl_pts_positions p, deleted d
where p.poid = d.PoID
and p.fname <> d.fname
or p.lname <> d.lname)
Begin
Insert Into tbl_pts_positions_history
(
PoID,fname,lname,city,state,jobcode,
descr,temp_ends,Eff_Date,Comments,Trans_Time
)
Select *, GetDate() From Deleted
End
-
You don't need to join to the base tables, just compare inserted & deleted:
Create Trigger tbl_pts_positions_Update_01_Tr
On tbl_pts_positions After Update
As
Begin
Insert Into tbl_pts_positions_history
(
PoID,fname,lname,city,state,jobcode,
descr,temp_ends,Eff_Date,Comments,Trans_Time
)
Select d.*, GetDate()
From deleted As d
Inner Join inserted As i
On (i.poid = d.poid)
Where ( i.fname <> d.fname Or i.lname <> d.lname )
End
-
I should mention that I've tried a bunch of variations trying to get this to work, and one was using inserted/deleted instead of original/deleted. Anyway, I tried your code and it didn't work. I don't get any new history records for either case (where one should be created nor where one shouldn't be created).
Any other ideas?
-
I think you should go back to the answer you already got, actually.
I just set up one of these from scratch and it works fine, and now that I'm done, I'm unsurprised to see that the trigger I wrote is pretty close to the one posted earlier.
** This is the trigger I wrote -- which works, as demonstrated below.
CREATE TRIGGER [dbo].[trg_upd_positions] ON [dbo].[positions]
FOR UPDATE
NOT FOR REPLICATION
AS
INSERT INTO [dbo].[tbl_pts_positions_history] (PoID, fname, lname, Trans_Time)
SELECT d.[PoID], d.[fname], d.[lname], GETDATE()
FROM inserted i JOIN deleted d ON
i.[PoID] = d.[PoID] AND (i.[fname] d.[fname] OR i.[lname] d.[lname])
** Demonstration that it works, at least for me
truncate table [dbo].[tbl_pts_positions_history]
select * from [dbo].[tbl_pts_positions_history]
(empty)
select * from [dbo].[positions]
1jakemunsonboiseid
update [dbo].[positions] set [fname] = 'jacob' where [PoID] = 1
(1 row affected)
select * from [dbo].[positions]
1jacobmunsonboiseid
select * from [dbo].[tbl_pts_positions_history]
1jakemunsonNULLNULL2006-02-17 20:11:11.503
* NB: You may not get updates if you change fname to null, for example, due to your trigger not being written to handle null logic (that is to say, i.[fname]d.[fname] is not true if either is null). I would normally write null testing in, but I was just making it work for the simple logic you originally posted.
-
I recommend the INSTEAD OF TRIGGER
CREATE TRIGGER [dbo].[trg_upd_positions] ON [dbo].[positions]
INSTEAD OF UPDATE
SELECT *
FROM INSERTED [fields]
INNER JOIN [dbo].[positions] p
ON i.fld1 = p.fld1
WHERE i.fld2 p.fld2
UPDATE [dbo].[positions]
SET fld2 = i.fld2
FROM INSERTED i
INNER JOIN [dbo].[positions] p
ON i.fld1 = p.fld1
-
Perry,
The problem with that trigger is that it does insert a history record when it shouldn't. In my tests, if I update the position record, but don't change any of the trigger fields, a history record is still created.
-
You said the problem was "I don't get any new history records for either case", so I checked that I did in fact get history records.
Now you are saying that you are getting too many history records.
Which diagnosis is correct--I cannot tell?
-
Also, I don't see any extra history records when I test it. Do you get the same results I post below? Can you post the details (including queries) of the wrong results you see? (I always suggest posting an actual query, both text and results, when you are trying to describe what is happening -- because as you can see here, my sample does not show the behavior you described, so I don't know how to reproduce what you describe -- but if you gave me the sample queries, then I would know how to do so.)
** Demonstration that it works, at least for me
truncate table [dbo].[tbl_pts_positions_history]
truncate table [dbo].[positions]
select * from [dbo].[tbl_pts_positions_history]
(empty)
select * from [dbo].[positions]
(empty)
insert into [dbo].[positions] ( [PoID], [fname], [lname], [city], [state] ) VALUES ( 1, 'jake', 'munson', 'boise', 'id')
(1 row affected)
select * from [dbo].[positions]
1 jake munson boise id
update [dbo].[positions] set [fname] = 'jacob' where [PoID] = 1
(1 row affected)
select * from [dbo].[positions]
1 jacob munson boise id
select * from [dbo].[tbl_pts_positions_history]
1jakemunson2006-02-21 13:46:54.097
update [dbo].[positions] set [city] = 'ashton' where [PoID] = 1
(1 row affected)
select * from [dbo].[tbl_pts_positions_history]
1jakemunson2006-02-21 13:46:54.097
-
Thanks for your persistence Perry.
I have solved the problem, and it was a dumb mistake. The tests I was running had data with nulls in it (the production data allows nulls as well). The trigger was doing a comparison against null, which doesn't work. I added the IsNull function to the fields that were being compared (in the trigger), and now my trigger does what I planned it to do.
-
Yes, I believe that I added a warning to my first message, that your test would not work the way you probably want, if ANSI_NULLs is in effect.
Glad you have it resolved and working now.
-
Another mistake in your trigger:
You check IF EXISTS ... and insert whole set of lines, even not al of them are in this EXIST set.
It should be like this:
Insert Into tbl_pts_positions_history
( PoID,fname,lname,city,state,jobcode,
descr,temp_ends,Eff_Date,Comments,Trans_Time)
Select d.PoID, d.fname,d.lname,d.city,d.state,d.jobcode,
d.descr,d.temp_ends,d.Eff_Date,d.Comments, GetDate()
From Deleted d
Inner Join inserted i on i.poid = d.PoID
and (i.fname <> d.fname or i.lname <> d.lname) -- add IS NULL checks by yourself
_____________
Code for TallyGenerator -
Actually, I am only inserting one line. The trigger is from an update statement, and I am inserting from 'Deleted', that magical temporary table that contains the 'outgoing' record (pre-updated values in one record). Or am I missing something?
-
Another mistake is to treat trigger on a table as a part of your page.
It's not.
It's a part of database functionality and nobody can guarantee that tomorrow somebody else will not insert 2 lines in this table.
If you do something in database you must to do it properly.
_____________
Code for TallyGenerator -
Part of my page? I'm not doing anything with pages here.
I now see the hole in my logic though. I was under the assumption that the inserted and deleted tables only contain one record. If I were updating a bunch of records, there would be an 'inserted' and 'deleted' for each updated record. However, I found this sentence in an MSDN article: "[The inserted and deleted] tables are used to store the before and after state of the table that was affected by the INSERT, UPDATE, or DELETE statement that caused the trigger to fire in the first place." So the entire table is in there, not just one record. And thus, I need to add the equajoin.
Thanks for helping to point me in the right direction, Sergiy.
-
I don't see the hole in the logic guys.
(Let me requote the trigger, as this board is not great for navigation)
Where is the problem with multiple rows?
INSERT INTO [dbo].[tbl_pts_positions_history] (PoID, fname, lname, Trans_Time)
SELECT d.[PoID], d.[fname], d.[lname], GETDATE()
FROM inserted i JOIN deleted d ON
i.[PoID] = d.[PoID] AND (i.[fname] d.[fname] OR i.[lname] d.[lname])
Shouldn't that cover the cases of multiple changes, or multiple insertions?
But, it won't work correctly if multiple rows in table inserted have the same PoID -- is that possible? Is that what you guys mean?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply