Solved

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

Posted on 2008-10-21
3
793 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
[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
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Table create permissions on SQL Server 2005 9 45
SQL Error - Query 6 55
Using this function 4 54
How can I find this data? 3 41
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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