Trigger Query Deleted Row

Question

Trigger Query Deleted Row

drodriguez-762178

SSCommitted

Points: 1795
More actions
January 22, 2009 at 3:14 pm

#155165

SQL Gurus:
Is there a way to query or capture a record that is being deleted from a table in a trigger? I want to shoot off an email when a certain record gets deleted, but I am unsure on how to do it. This is what I have so far.
CREATE TRIGGER [HumanResources].[ecorp_down]
ON [HumanResources].[Department]
AFTER DELETE
AS
DECLARE @stat varchar(50)
SET @stat = SELECT NAME_FIELD FROM DELETED
Thanks,
Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

What you have here will work just fine. You can just make a call to msdb..sp_send_dbmail (assuming you have database mail set up) and send whatever information you would like to about the deleted row.

You could also create a separate audit type of table that will capture the deletes for you, if that is something that is important.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 2

drodriguez (1/22/2009)
SQL Gurus:
Is there a way to query or capture a record that is being deleted from a table in a trigger? I want to shoot off an email when a certain record gets deleted, but I am unsure on how to do it. This is what I have so far.
CREATE TRIGGER [HumanResources].[ecorp_down]
ON [HumanResources].[Department]
AFTER DELETE
AS
DECLARE @stat varchar(50)
SET @stat = SELECT NAME_FIELD FROM DELETED
Thanks,
Dave

That will work perfectly well as long as only one record is being deleted. If you want to be able to handle multiples, you might try something like:

declare @stat varchar(max)

select @stat = isnull(@stat, '') = name_field + ', ' from deleted

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

drodriguez-762178 SSCommitted Points: 1795 More actions · Answer 3

Thanks. Is there a way to see this in SQL 2005 profiler?

Yuri55 SSCrazy Eights Points: 8441 More actions · Answer 4

Not sure regarding Profiler but if you would like to keep track of all deleted names then 1) create table 2) insert all deleted records into it using OUTPUT. You can call this table any time.