Solved

T-SQL: trigger instead of delete doesn't work when i run a bulk delete operation

Posted on 2008-10-21
3
780 Views
Last Modified: 2012-08-14
I have created a trigger for 'instead of delete' that gets some data from two tables, inserts part of the deleted row in a log table and then deletes the row. The trigger works when i manually delete a row from the table, the problem is that if i run a query to delete multiple rows then it only works for the last row to be deleted. So if a query is supposed to delete 5 rows and do all the operations in the trigger for each one of them, it only does it for the last one. My guess is that it's trying to run the following trigger before it is finished with the first and that is creating a problem, any ideas? code attached below.



ALTER TRIGGER trigger1

   ON TABLE1

   INSTEAD OF DELETE

AS 

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	

	DECLARE @var1		varchar(20)

	DECLARE @var2		datetime

	DECLARE @var3		datetime

	DECLARE @var4	        datetime

	DECLARE @var5		TINYINT

	DECLARE @var6		VARCHAR(10)

	

	SET @var2 = getDate()
 

	SELECT @var1 = uid, @var3 = var3, @var4 = var4, @var5 = var5 FROM DELETED	

	

	IF @var3 < 1 BEGIN 

		INSERT INTO TABLE2 (var1, var2, var3, var4, var5) 

			VALUES (@var1, @var2, @var3, @var4, @var5)

	END

	

	DELETE FROM TABLE1 WHERE uid = @var1

END

Open in new window

0
Comment
Question by:LDIT
  • 2
3 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
ID: 22766898
A trigger fires for each transaction, not for each row.  Your trigger assumes there's only a single row.

Try this:

ALTER TRIGGER trigger1

   ON TABLE1

   INSTEAD OF DELETE

AS 

BEGIN

  INSERT INTO TABLE2 (var1, var2, var3, var4, var5)

    (Select uid, getdate(), var3, var4, var5 from DELETED Where var3 < 1)
 

  Delete From Table1

   From Table1 Inner Join Deleted on Table1.uid = deleted.uid

END

Open in new window

0
 

Author Closing Comment

by:LDIT
ID: 31508239
Cool, i thought the deleted was not a table, but rather a row. So this means the deleted table holds all the deleted rows for the transaction? Thanks for the solution!
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22768187
DELETED and INSERTED are tables ... well, not real tables, but they look like tables when within a trigger.  I'm not sure of the technical term applied.

The assumption that they have only a single record is a common trigger problem.    (Yes, I've done it too ...)

All that is within MS SQL Server ... MySQL has significantly different syntax in that area, and I'm not sure exactly what it's doing there.

Glad to help!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now