CODEWITHSUNDEEP
sqlsql-servertriggers
Jędrzej
Jędrzej

Reputation: 49

Sql server Trigger deleting few rows at once

I wrote a trigger which on deleting row copy values of it and paste them in another table but my task is also to make that function (I don't know if it should be a trigger) work when multiple rows are deleted and I don't know how should I approach that. 

CREATE TRIGGER copy ON person FOR DELETE
AS
DECLARE @firstname VARCHAR(20), @lastname VARCHAR(35), @pesel CHAR(11), @birthdate DATE, @salary MONEY
SELECT @firstname=firstname, @lastname=lastname, @pesel=pesel, @birthdate=birthdate, @salary=salary FROM deleted
INSERT INTO person_history VALUES(@firstname,@lastname,@pesel,@birthdate,@salary, 'D', GETDATE()) 
GO

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

Use insert . . . select:

INSERT INTO person_history (  . . . ) 
    SELECT firstname, lastname, pesel, birthdate, salary, 'D', GETDATE()
    FROM deleted;

The . . . is for you to include the columns being inserted. This is a best practice.

Upvotes: 4

Related Questions