Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trigger on Delete

Posted on 2014-03-14
4
Medium Priority
?
267 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 920 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

972 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