CODEWITHSUNDEEP
sqlsql-servertriggersrollback
kukken
kukken

Reputation: 13

sql DELETE trigger won't rollback

The trigger is supposed to rollback if the statement in the SELECT exists. The thing is, it doesn't. When I only run the SELECT from the trigger, it shows that a row exists. But when I try DELETE with the same values as I have hard coded in the trigger, the trigger fires but it does not do a rollback. Anyone got any ideas what might be wrong? 

CREATE TRIGGER trg_del ON Projektbefattningar

FOR DELETE
AS

  SELECT @befNr = Befattningsnr, @pNr = pNr, @EtappNr = Etappnr FROM deleted
-- Not currently using these. Using hard coded values to illustrate my problem

    IF EXISTS (
        SELECT *
        FROM projektbefattningar
        WHERE Befattningsnr = 2 AND pNr = 1 and Etappnr = 1 AND Anställningsnr is not null
        )
    BEGIN
        RAISERROR('Could not delete, Anställningsnr is not null', 16, 1)
        --THROW 50001, 'Could not delete, Anställningsnr is not null', 1;
        ROLLBACK TRANSACTION;
    END

GO

Upvotes: 1

Views: 2342

Answers (2)

Rafael Leandro Ferreira
Rafael Leandro Ferreira

Reputation: 23

When you use FOR DELETE you consider that this TRIGGER will fired after the delete statement is complete. To Verify a condition before DELETE you need to use INSTEAD OF DELETE. Your Trigger declaration will be:

CREATE TRIGGER trg_del ON Projektbefattningar
INSTEAD OF DELETE
AS

Now To Confirm your delete statement you need to include the delete alghouth it will not be deleted. The final procedure will be like this:

CREATE TRIGGER trg_del ON Projektbefattningar

INSTEAD OF DELETE
AS

  SELECT @befNr = Befattningsnr, @pNr = pNr, @EtappNr = Etappnr FROM deleted
-- Not currently using these. Using hard coded values to illustrate my problem

    IF EXISTS (
        SELECT *
        FROM projektbefattningar
        WHERE Befattningsnr = 2 AND pNr = 1 and Etappnr = 1 AND Anställningsnr is not null
        )
    BEGIN
        RAISERROR('Could not delete, Anställningsnr is not null', 16, 1)
        --THROW 50001, 'Could not delete, Anställningsnr is not null', 1;
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN

       DELETE FROM YourTableToDelete Where idOfYourTable in ( Select idOfYourTable from Deleted )
    END

GO

Upvotes: 2

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

Try this

CREATE TRIGGER trg_del ON Projektbefattningar
FOR DELETE
AS
SELECT @befNr = Befattningsnr
    ,@pNr = pNr
    ,@EtappNr = Etappnr
FROM deleted

-- Not currently using these. Using hard coded values to illustrate my problem
IF EXISTS (
        SELECT 1
        FROM deleted
        WHERE Befattningsnr = 2
            AND pNr = 1
            AND Etappnr = 1
            AND Anställningsnr IS NOT NULL
        )
BEGIN
    RAISERROR (
            'Could not delete, Anställningsnr is not null'
            ,16
            ,1
            )

    --THROW 50001, 'Could not delete, Anställningsnr is not null', 1;
    ROLLBACK TRANSACTION;
END
GO

Upvotes: 1

Related Questions