Instead of Delete trigger

Question

I want to pass one validation DML statement on the trigger. Condition is below DML statement

select distinct t1.cid 
from table1 t1, table t2 
where t1.cid=t2.cid 
      and 
      t1.status<>'5'

if table1.status is not 5, then trigger will block delete transaction.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
INSTEAD OF DELETE
AS
Begin
    declare @caseid VARCHAR(10)
    select @caseid = deleted.cid from deleted
    If exists(select * from table1 t1,table2 t2 where t1.cid=t2.cid AND t1.STATUS<>'5' AND t2.cid=@caseid)
    begin
        Rollback Transaction
        RAISERROR ('Deletions not allowed after loaded ', 16, 1) 
    end
end

Will the above trigger will give correct solution?

Answer 1

An INSTEAD OF trigger is not the best solution here. An INSTEAD OF trigger performs no actions against the database unless explicitly done so in the trigger code. Your example will never delete rows regardless of the STATUS value because it contains no DELETE statement. The ROLLBACK is superfluous since no modifications were made.

Consider an AFTER DELETE trigger for this requirement. As with any trigger, multiple rows may be affected so it is important to handle such a case. Avoid using the select @caseid as that will identify only a single cid value even if different ones were deleted.

Here's an example of an after trigger for your need.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
FOR DELETE
AS
IF EXISTS(SELECT * FROM deleted where deleted.STATUS <>'5')
BEGIN
        RAISERROR ('Deletions not allowed after loaded ', 16, 1);
        ROLLBACK TRANSACTION;
END;
GO