CODEWITHSUNDEEP
sql-server-2008
Muflix
Muflix

Reputation: 6768

Trigger insert deleted row

Hello I have some difficulties with simple trigger in MSSQL 2008.

I want to insert deleted row to another table.

Here is my trigger code:

use databasex;
GO
CREATE TRIGGER DeleteEmployee
ON Employees
 FOR DELETE 
AS
 BEGIN
    INSERT INTO DeletedEmployees (Name, Surname, Function) 
    VALUES (deleted.Name, deleted.Surname, deleted.Function)
 END

Employees table:

 Name  Surname Function ...

DeletedEmployees table:

Name Surname Function ...

I need somehow to specify that deleted columns are from deleted table, but how ?

I dont want to write subquery for every column.

How it being done ?

ps: is there any good tutorial on using triggers ? i believe i will need write more triggers in the future.

Upvotes: 0

Views: 136

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

DELETED is a virtual table you select from (you may have deleted more than one row, so a single value is not enough)

Also, FUNCTION is a reserved word and needs to be escaped

CREATE TRIGGER DeleteEmployee
ON Employees
 FOR DELETE 
AS
 BEGIN
    INSERT INTO DeletedEmployees (Name, Surname, [Function]) 
      SELECT Name, Surname, [Function] FROM deleted
 END

An SQLfiddle to test with.

Upvotes: 2

Related Questions