SQL Server Trigger – FOR DELETE

deletesql servertrigger

I want to create a trigger, which does not delete a customer if his name is Peter. How can I do that?

CREATE TRIGGER trgCUSTOMER ON customer
FOR DELETE
AS
BEGIN
    IF (SELECT name FROM deleted) not like 'Peter'
    BEGIN
        delete from customer where name = (select name from deleted);
    END
END;

Best Answer

You have two options:

1) Rollback the DELETE in case you're deleting the row "Peter". You do this with an AFTER trigger. Be aware that it will rollback the whole transaction in case yuo're deleting multiple rows among which at least one is "Peter".

CREATE TRIGGER TR_CUSTOMER_DEL ON customer
FOR DELETE
AS
BEGIN

    SET NOCOUNT ON;

    IF EXISTS (
        SELECT *
        FROM deleted
        WHERE name = 'Peter'
    )
    BEGIN
        ROLLBACK;
        THROW 50001, 'Cannot delete Peter', 1;
    END
END;

2) You override the way SQL Server does DELETEs on the table, using an INSTEAD OF trigger. In this case, you silently ignore DELETEs for the row "Peter".

CREATE TRIGGER TR_CUSTOMER_IO_DEL ON customer
INSTEAD OF DELETE
AS
BEGIN

    SET NOCOUNT ON;

    DELETE c
    FROM customer AS c
    INNER JOIN deleted AS d
        ON c.customer_id = d.customer_id
    WHERE d.name <> 'Peter';

END;

Which one is best for you highly depends on your business logic.

Related Question