BartWestphal
asked on
Delete Trigger Help
I have very limited experience with triggers, but with the help of this site (I modified the answer to question 26082733) I was able to create a trigger that watches a table of email addresses for my customers and records when there is an Insert, Update, or Delete. This seems to be working great for the inserts and updates, but the deletes don't have enough information to make them actually be useful. In particular - our customer number is called the CUST_EDP_ID, when the delete occurs, the CUST_EDP_ID is not being captured by the trigger - it's coming out as NULL. Even the EMAIL address that was deleted is not captured. In a nutshell, how can I change this to capture the CUST_EDP_ID and EMAIL data for deletes? This way we know which EMAIL was deleted. The code and a screen cap sample of data are below.
Also, can you explain what the i.fieldnames means in this section of code (oh I bet this is important - lol)?
i.CUST_EDP_ID, i.EMAIL_ADDR_CD
Thanks!
-------------------------- --
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_EMAIL] on [dbo].[SV_MACORD_CUST_EMAI L]
FOR insert, update, delete
as
insert into dbo.Changes_EMAIL (Record_SLOTID, ChangeDate, MoveDate, CUST_EDP_ID, EMAIL_ADDR_CD, ChangeType)
select IsNull(i.SLOTID, d.SLOTID), getdate(), NULL, i.CUST_EDP_ID, i.EMAIL_ADDR_CD,
case when i.SLOTID = d.SLOTID then 'U'
when i.SLOTID is not null then 'I'
else 'D' end
from inserted i
full outer join deleted d on i.SLOTID = d.SLOTID
-------------------------- ---------- ---------- -
Also, can you explain what the i.fieldnames means in this section of code (oh I bet this is important - lol)?
i.CUST_EDP_ID, i.EMAIL_ADDR_CD
Thanks!
--------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_EMAIL] on [dbo].[SV_MACORD_CUST_EMAI
FOR insert, update, delete
as
insert into dbo.Changes_EMAIL (Record_SLOTID, ChangeDate, MoveDate, CUST_EDP_ID, EMAIL_ADDR_CD, ChangeType)
select IsNull(i.SLOTID, d.SLOTID), getdate(), NULL, i.CUST_EDP_ID, i.EMAIL_ADDR_CD,
case when i.SLOTID = d.SLOTID then 'U'
when i.SLOTID is not null then 'I'
else 'D' end
from inserted i
full outer join deleted d on i.SLOTID = d.SLOTID
--------------------------
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
angellll, this solution worked perfectly! Thank you for the quick, effective, and clear answer. And I have some new knowledge too!
- Bart
- Bart
when i.SLOTID is not null then 'I'
else 'D' end
from inserted i
it is from the inserted/Updated record.
so it will have the new value that is in the table for that field.
I think it may be easier to make another trigger just for deletes and keep the update inserts in the original trigger.