CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Michal
Michal

Reputation: 31

Simple Trigger Beginner Prevent from delete

I am trying to write my first trigger. I have table of elements. I have few records inside of it. Few records have checkbox on 1 and few on 0.

I am trying to make trigger that will prevent from delete records that have checkbox on 1 (elm_intcolumn1).

Trigger is working but i can't delete records with checkbox 0. Trigger is blocking all records on my elements. 

ALTER TRIGGER [dbo].[test]
    ON [dbo].[elements]
    INSTEAD OF DELETE
AS
BEGIN
    SELECT CASE
        WHEN elm_intcolumn1 = 1
            THEN 0
        WHEN elm_intcolumn1 = 0
            THEN 1
    END
    FROM elements
    BEGIN
        RAISERROR ('błąd', 16, 1)
        ROLLBACK TRANSACTION
        RETURN;
    END
END

Upvotes: 2

Views: 197

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26896

You're overcomplicating your solution.

First of all - you don't need to check table on which trigger was created, instead check special table named deleted available in the context of instead of delete trigger. This table contains exactly the records you're trying to delete.

Second - your check should be simply "is there records having "elm_intcolumn1 = 1" in deleted table". If yes - then throw exception, if no such a records - then delete from the table all the records from deleted. Your table should has some primary key in this case (I'm assuming is it named ID in the sample below).

So literally it is something like:

if exists(select * from deleted where elm_intcolumn1 = 1)
begin
    RAISERROR ('błąd', 16, 1)
    ROLLBACK TRANSACTION
    RETURN;
end
else
begin
    delete from elements where ID in (select ID from deleted)
end

Notice you have to explicitly delete records from your table in this trigger since it is instead of delete, not after delete.

Alternatively you can change trigger type to after delete so explicit deletion in else condition branch will not be needed.

Upvotes: 0

Devart
Devart

Reputation: 122042

Check about virtual tables DELETED and INSERTED -

CREATE TABLE dbo.[elements] (
    id INT IDENTITY PRIMARY KEY,
    elm_intcolumn1 BIT
)
GO
INSERT INTO dbo.[elements] (elm_intcolumn1)
VALUES (0), (1)
GO

ALTER TRIGGER dbo.test
    ON dbo.[elements]
    AFTER DELETE
AS
BEGIN
    IF EXISTS(
        SELECT 1
        FROM DELETED
        WHERE elm_intcolumn1 = 1
    )
    BEGIN
        RAISERROR ('revert', 16, 1)
        ROLLBACK TRANSACTION
    END
END
GO

SELECT * FROM dbo.[elements]

DELETE dbo.[elements]
WHERE id = 2

When delete second record:

Msg 50000, Level 16, State 1, Procedure test, Line 39
revert
Msg 3609, Level 16, State 1, Line 29
The transaction ended in the trigger. The batch has been aborted.

Upvotes: 1

Related Questions