Solved

Trigger on Delete

Posted on 2014-03-14
4
260 Views
Last Modified: 2014-03-15
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/
0
Comment
Question by:yadavdep
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39928876
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928886
inside the trigger, you have access to the virtual tables DELETED which contains the rows deleted by the statement that raised the trigger.
0
 
LVL 13

Accepted Solution

by:
magarity earned 230 total points
ID: 39929687
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39931033
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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