AFTER Trigger

SalesMan Account
SID AcctType AID AID AcctName
1 0 1 1 myName
2 1 2 2 hisName
I need a trigger that when the AcctType is set to 2, it will delete record 2
(AID 2)(hisName) in the Account table.
Hi
create trigger my_tr on SalesMan for update
as
begin
if update(Acctype)
begin
delete Account where id in (select id from deleted where
deleted.aid=Account.aid)
end
end
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
> SalesMan Account
> SID AcctType AID AID AcctName
> 1 0 1 1 myName
> 2 1 2 2 hisName
> I need a trigger that when the AcctType is set to 2, it will delete record
> 2
> (AID 2)(hisName) in the Account table.
|||I think this trigger does not specify that AcctType has been changed to 2,
so may delete more than expected.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ewomg08kIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hi
> create trigger my_tr on SalesMan for update
> as
> begin
> if update(Acctype)
> begin
> delete Account where id in (select id from deleted where
> deleted.aid=Account.aid)
> end
> end
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
>
|||> when the AcctType is set to 2
When which AcctType is set to 2? The row with SID 1? The row with SID 2?
If SID 1, why should it delete SID 2? What shows me that rows 1 and 2 are
related in any way?
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
> SalesMan Account
> SID AcctType AID AID AcctName
> 1 0 1 1 myName
> 2 1 2 2 hisName
> I need a trigger that when the AcctType is set to 2, it will delete record
> 2
> (AID 2)(hisName) in the Account table.
|||Good catch Aaron, I have just missed it :-))
create table t1 (c int not null primary key,c2 int)
insert into t1 values (1,1)
insert into t1 values (2,10)
insert into t1 values (3,20)
insert into t1 values (4,30)
create table t2 (c int ,c2 char(1))
insert into t2 values (1,'a')
insert into t2 values (1,'b')
insert into t2 values (2,'c')
insert into t2 values (3,'d')
insert into t2 values (4,'f')
alter trigger my_tr on t1 for update
as
begin
if update(c2)
begin
delete t2 where exists (select * from
inserted i where i.c=t2.c and i.c2=2)
end
end
update t1 set c2=20 where c=1
--did not delete
select * from t2
update t1 set c2=2 where c=1
--does
select * from t2
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uJgw9Q$kIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I think this trigger does not specify that AcctType has been changed to 2,
>so may delete more than expected.
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ewomg08kIHA.1204@.TK2MSFTNGP03.phx.gbl...
>