Table Trigger to delete all rows before Inserting Rows

Hi All,

I have a SQL script to insert data into a table as below:( I am using Linked Servers scenario )

INSERT into [SRV1\INS2].BB.dbo.Agents2
select * from [SRV2\INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.

I had below Table Trigger on [SRV1\INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--USE BB
ALTER TRIGGER [dbo].[MyTrigger] ON [dbo].[Agents2]
For INSERT
AS
BEGIN
    DELETE From Agents2
END

Appreciate any advice.

Regards

Mira

July 24th, 2015 1:57am

Why don't you simply write a DELETE/TRUNCATE statement before the insert? If you want to do this at all with a trigger here is how you can do it. 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--USE BB
ALTER TRIGGER [dbo].[MyTrigger] ON [dbo].[AGENTS2]
INSTEAD OF INSERT
AS
BEGIN
    DELETE From AGENTS2
    INSERT INTO AGENTS2 SELECT * FROM INSERTED
END

Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 2:10am

Hi,

As suggested by Satheesh, I too advice you to write a delete statement before insert instead creating trigger to delete.

For better code management, you can also make use of Error Handling (TRY/CATCH) logic.

SQL SERVER Disadvantages (Problems) of Triggers

July 24th, 2015 2:31am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics