Trigger on Delete

I have table Contacts_Holders.
I am calling one SP to delete records from this table.
the SP take two parameters @ContactID, @HolderID

I need to write a trigger for delete on this table that will insert a record in different and put @ContactID and @holderid in to this other table.

I need to know how can my trigger takes the values which I Pass from SP and insert them into an another table on Delete/
yadavdepAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
magarityConnect With a Mentor Commented:
Is the trigger required to only copy values when the procedure does a delete or when any process does a delete?  Why can't you just make a regular on delete trigger like this:

create trigger my_trigger on table after delete as
declare @ContactID int;
declare @HolderID int;
select @ContactID = ContactID, @HolderID = HolderID from deleted;
insert into othertable (ContactID, HolderID) values (@ContactID, @HolderID);

Open in new window


This would move any delete, not just deletes from the procedure though.  If you have a requirement to only trap the ones from the procedure, why not build that into the procedure itself instead of a trigger?
0
 
Carl TawnSystems and Integration DeveloperCommented:
You can't access the parameters directly from the trigger. But presumably those values somehow relate to the row you are deleting - how do they relate?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
inside the trigger, you have access to the virtual tables DELETED which contains the rows deleted by the statement that raised the trigger.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: the above will ONLY work correctly if a single line is deleted.
proper (and more efficient) code would be this:
create trigger my_trigger on table after delete 
as
insert into othertable (ContactID, HolderID) 
select ContactID, HolderID 
from Deleted

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.