locked
Table Trigger to delete all rows before Inserting Rows RRS feed

  • Question

  • 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

    Friday, July 24, 2015 5:44 AM

Answers

  • 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


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Olaf HelperMVP Friday, July 24, 2015 6:13 AM
    • Marked as answer by Mira Pimbi Sunday, July 26, 2015 11:32 PM
    Friday, July 24, 2015 5:56 AM
  • 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


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Marked as answer by Mira Pimbi Sunday, July 26, 2015 11:32 PM
    Friday, July 24, 2015 6:17 AM

All replies

  • 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


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Olaf HelperMVP Friday, July 24, 2015 6:13 AM
    • Marked as answer by Mira Pimbi Sunday, July 26, 2015 11:32 PM
    Friday, July 24, 2015 5:56 AM
  • 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


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Marked as answer by Mira Pimbi Sunday, July 26, 2015 11:32 PM
    Friday, July 24, 2015 6:17 AM