Newable On Trigger

[vtops]

Dear All,

I have made this trigger

create trigger test
on mixanaki
after update
as
if update (flag1)
update mixanaki set flag2=flag1
where mixanaki.[id]=(select [id] from inserted)


My Problem Are the following:

1. If i try to update more than one rows on table mixanaki the trigger doesn't work With Error Message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


2. Is it possible if the flag1 doesn't change value on update the trigger don't be enabled.


I mean if the flag1 has value=1 and then i run a command: update mixanaki set flag1=1 where id=.... in this case i don't want to be enabled the triger


Thanks a Lot and best Regards

 

[vongrunt]

> 1. If i try to update more than one rows on table mixanaki the trigger doesn't work With Error Message:

Because (contrary to popular ignorant opinion) triggers fire once per DML statement, not once per row affected.

> 2. Is it possible if the flag1 doesn't change value on update the trigger don't be enabled.

Another good question. UPDATE() does not check whether old value is different than new one.

Try something like:

create trigger test
on mixanaki
after update
as
update M
set flag2=flag1
from mixanaki M
inner join inserted I on M.id = I.id
inner join deleted D on M.id = D.id
where I.flag1<>D.flag1

Btw. this can be simplified... guess how?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vtops]

Thank you vongrunt

It is exactly that i was needing

Best Regards