Solved

Delete Trigger Help

Posted on 2010-11-10
3
306 Views
Last Modified: 2012-05-10
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_EMAIL]
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
-----------------------------------------------

 outputexample
0
Comment
Question by:BartWestphal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34103295
you have to use the ISNULL which you used on SLOTID also for the other columns
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_CHANGES_EMAIL] on [dbo].[SV_MACORD_CUST_EMAIL]
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
  , isnull(i.CUST_EDP_ID, d.CUST_EDP_ID)
  , isnull(i.EMAIL_ADDR_CD, d.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

Open in new window

0
 
LVL 14

Expert Comment

by:Emes
ID: 34103314
case when i.SLOTID = d.SLOTID then 'U'
   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.

0
 

Author Closing Comment

by:BartWestphal
ID: 34104081
angellll, this solution worked perfectly!  Thank you for the quick, effective, and clear answer.  And I have some new knowledge too!
- Bart
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question