create table ta(id int,[order] int,state bit)
insert ta
select 1,20 ,1 union all
select 2,30 ,1
create table tb(id int,qty bit)
insert tb
select 1,1 union all
select 2,1
create trigger test_tr on ta
instead of delete
as
update ta
set ta.state=0
from deleted d inner join ta on d.id=ta.id
update tb
set qty=0
from deleted d inner join tb on d.id=tb.id