CODEWITHSUNDEEP
sql-server-2005triggers
Alaa Jabre
Alaa Jabre

Reputation: 1883

Insert deleted rows in trigger

I need to insert the deleted rows from table Table_A to Table_B which have the same structure
(something like recycle bin) but i don't have much experience in SQL SERVER Triggers 

CREATE TRIGGER dbo.Table_A_Delete_Instead_Of_Trigger
ON dbo.Table_A
INSTEAD OF DELETE
AS
BEGIN    
     INSERT INTO Table_B 
     SELECT T 

     DELETE T                  
END

please spare my lake of experience. Thanks.

Upvotes: 0

Views: 2248

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

You don't need an 'INSTEAD OF' trigger, you need an 'AFTER' trigger.

Given a table TableA...

CREATE TABLE [dbo].[TableA](
    [Col1] [varchar](50) NOT NULL,
    [Col2] [varchar](50) NOT NULL
) ON [PRIMARY]

and a corresponding TableB...

CREATE TABLE [dbo].[TableB](
    [Col1] [varchar](50) NOT NULL,
    [Col2] [varchar](50) NOT NULL
) ON [PRIMARY]

Then the following trigger will do what you wish:

CREATE TRIGGER trg_TableA_Archive 
ON  TableA
AFTER DELETE
AS 

    INSERT INTO TableB
    SELECT *
    FROM deleted
GO

Upvotes: 6

Mithrandir
Mithrandir

Reputation: 25337

Try this:

CREATE TRIGGER dbo.Table_A_Delete_Instead_Of_Trigger
ON dbo.Table_A
AFTER DELETE
AS
BEGIN    
     INSERT INTO Table_B 
     SELECT * FROM 
     DELETED;          
END

I think you don't need an instead of trigger in this case.

Upvotes: 2

Related Questions