CODEWITHSUNDEEP
sql-server-2008t-sql
user168507
user168507

Reputation: 881

Delete trigger on multiple rows

Below is my trigger:

ALTER TRIGGER [dbo].[trgPaxeDeleted]
   ON  [dbo].[paxe] 
   AFTER DELETE
AS 
declare
@HFflightID int,
@RFflightID int

BEGIN
        Select @HFflightID = hfFlightID, @RFflightID = rfFlightID from deleted

        -- Hinflug: flugKontingent hochsetzen --   
        UPDATE    flightdata
        SET       flightQuota = flightQuota + 1
        WHERE     (flightID = @HFflightID)

        -- Rückflug: flugKontingent hochsetzen --
        UPDATE    flightdata
        SET       flightQuota = flightQuota + 1
        WHERE     (flightID = @RFflightID)

END

This works fine on deleting a single row. But when deleting multiple rows only one trigger operation is done.

How do I change this?

Upvotes: 4

Views: 13954

Answers (5)

anesupaul-developer
anesupaul-developer

Reputation: 91

I was facing the same issue and i found an easy solution.

First I created the Trigger Table to store deleted columns

CREATE TABLE dbo.XLogs (
  Field1 varchar(30),
  Field2 varchar(30),
  Field3 varchar(30),
  Field4 varchar(30),
  Field5 varchar(2)
);

Then I created the trigger

CREATE TRIGGER dbo.TXLogs ON dbo.Table
AFTER DELETE
AS
BEGIN
   DECLARE @Id varchar(30)
   DECLARE @Field1 varchar(30)
   DECLARE @Field2 varchar(30)
   DECLARE @Field4 varchar(30)

   SELECT Id,Field1,Field2 INTO #DeletedTempTable FROM deleted

   WHILE(EXISTS(SELECT Id,Field1,Field2 FROM #DeletedTempTable))
   BEGIN
      SET @Field4 = 'Delete Row'
      SELECT TOP 1 @Id =Id , @Field1 = Field1, @Field2 = Field2 FROM #DeletedTempTable

      INSERT INTO dbo.XLogs VALUES (@Id,@Field1,@Field2,@Field4,'0')

      DELETE FROM #DeletedTempTable WHERE Id = @Id
   END
END

I created the temporary table to store my initial results, then loop through them using the while loop.

I selected TOP 1 to list only one row, read the data and stored it in my log table before I deleted the loop row and then loop again to get the next line. Same logic can be used for updated rows just change the FROM deleted to FROM inserted and other lines etc

Upvotes: 0

Fredy Marin
Fredy Marin

Reputation: 11

I base this example and do the removal with the primary key

CREATE TRIGGER On_Delete 
 ON tblActivityArchive AFTER DELETE 
 AS   BEGIN 
     DELETE dbo.tblJobLinesArchive 
       FROM dbo.tblJobLinesArchive a
      INNER JOIN Deleted d ON a.ActivityRef = d.ActivityRef
END

http://www.sqlservercentral.com/Forums/Topic484314-338-1.aspx

Upvotes: 1

Babulal Sah
Babulal Sah

Reputation: 11

You can delete multiple rows. But you have to put multiple id. For example:

DELETE FROM  VisitorMaster WHERE Id=85 or Id=86

Upvotes: 1

user168507
user168507

Reputation: 881

The delete trigger changed to

ALTER TRIGGER [dbo].[trgPaxeDeleted]
   ON  [dbo].[paxe] 
   AFTER DELETE
AS 
declare
@HFflightID int,
@RFflightID int

BEGIN

    SET NOCOUNT ON;     

    UPDATE    flightdata         
    SET       flightQuota = flightQuota + 1         
    WHERE     flightID IN (SELECT hfFlightID FROM deleted)

        UPDATE    flightdata         
    SET       flightQuota = flightQuota + 1         
    WHERE     flightID IN (SELECT rfFlightID FROM deleted)

END

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

SQL Server triggers fire one instance of the trigger for all rows; the deleted table will contain all rows being updated, so you can't store a single instance in a variable.

Get rid of the following line of code:

Select @HFflightID = hfFlightID, @RFflightID = rfFlightID from deleted

Your update statment should look like this:

UPDATE    flightdata         
SET       flightQuota = flightQuota + 1         
WHERE     flightID IN (SELECT hfFlightID FROM deleted)

UPDATE    flightdata         
SET       flightQuota = flightQuota + 1         
WHERE     flightID IN (SELECT rfFlightID FROM deleted)

Upvotes: 10

Related Questions