?
Solved

Trigger on Delete

Posted on 2014-03-14
4
Medium Priority
?
269 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

579 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