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?
Best Answer
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 theSTATUS
value because it contains noDELETE
statement. TheROLLBACK
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 theselect @caseid
as that will identify only a singlecid
value even if different ones were deleted.Here's an example of an after trigger for your need.