Alter a Delete Trigger to Check a Column Value

Question

I'm working with a database that has a trigger to prevent deletion of records in a certain table by raising an error and not committing the delete. I need to modify the behavior to allow the delete if a column of the record to be deleted has a specific value.

Here is the current code:

CREATE TRIGGER [dbo].[MyTable_PreventDelete] 
ON [dbo].[MyTable]
INSTEAD OF DELETE
AS
    -- TODO: Only run the code below if Deleted = 0
    ROLLBACK
    RAISERROR('ERROR: That column may not be deleted.',16,1)
    RETURN
GO

I tried to simply wrap the error call in a conditional, but it appears that I can't simply reference the column of the affected row directly:

...
CREATE TRIGGER [dbo].[MyTable_PreventDelete] 
ON [dbo].[MyTable]
INSTEAD OF DELETE
AS
IF IsDeleted = 0
BEGIN
    ROLLBACK
    RAISERROR('ERROR: That column may not be deleted.',16,1)
    RETURN
END
GO

What behavior do you want if ome of the records in the batch are deletable and some are not? In SQL Server, triggers operate on batches od data not single rows. — HLGEM, Jun 04 '13 at 14:11
SQL Server 2005. If any record in the batch has Deleted=0, the entire transaction should fail — TodoCleverNameHere, Jun 04 '13 at 14:59

score 1 · Accepted Answer · answered Jun 12 '13 at 14:00

After more investigation, here's what was done. There are 3 temp tables (INSERTED, UPDATED, DELETED) referenced in each trigger for those actions. In this case, we'll check the records affected in the DELETED (which reflect the records to be affected in the batch). If any does not have the flag set, the error is raised:

IF EXISTS(SELECT 1 FROM DELETED d WHERE d.Deleted=0)
BEGIN
    ROLLBACK
        -- the rollback is unnecessary
        RAISERROR('ERROR: That column may not be deleted.',16,1)
    END
    ELSE
        BEGIN
        DELETE c
        FROM [dbo].[MyTable] c
        INNER JOIN DELETED d
    ON c.PrimaryKey = d.PrimaryKey
    END

Alter a Delete Trigger to Check a Column Value

1 Answers1

Linked