September 20, 2018 at 9:12 am
Hi,
I have a pretty good understanding triggers, but I came across something that I do not understand.
If I am doing a delete I know I reference the deleted, or if I am doing an insert I would reference the inserted. However, I inherited this AFTER UPDATE trigger that uses both in it at the same time and I am not getting it.
Can someone please explain why the delete would be here and what it is doing?
Notice the reference in the join to deleted?
Here is the code:
IF UPDATE([agy_fee])
INSERT INTO Changes
(file_no, table_name, changed_from,
changed_to,
changed_column, created, createdby, Dh_CHIndex)
SELECT i.file_no, 'DebtorHistory', ISNULL(CAST(d.[agy_fee] as varchar ), '<NULL>'),
ISNULL(CAST(i.[agy_fee] as varchar ), '<NULL>'),
'[agy_fee]', CURRENT_TIMESTAMP, suser_sname(), i.DH_INDEX
FROM Inserted i
LEFT OUTER JOIN Deleted d on i.file_no = d.file_no
WHERE ( d.[agy_fee] <> i.[agy_fee]
OR (d.[agy_fee] IS NULL AND i.[agy_fee] IS NOT NULL)
OR (d.[agy_fee] IS NOT NULL AND i.[agy_fee] IS NULL)
)
Thank you
September 20, 2018 at 10:04 am
An update is logically equivalent to deleting the old records and inserting the new records. The deleted virtual table contains the old records and the inserted virtual table contains the new records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2018 at 10:09 am
Okay thanks that answers that
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply