Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger on Delete

Posted on 2014-03-14
4
Medium Priority
?
264 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
[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
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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 …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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…

722 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