create trigger insr on a for insert as if (select item from inserted)>0 insert into b select item from inserted else if (select item from inserted)<0 insert into c select item from inserted
create trigger trigger_name on a for insert as select a.itme ... from inserted if a.itme >0 begin insert into b values(... ...) end else begin insert into c values(... ...) end
bbsuner的不能运行,大家没看出来?
怎么不考虑多记录的可能?create trigger insr on a for insert asinsert into b select item from inserted where inserted.item>0 insert into c select item from inserted where inserted.item<0return
对了,4no兄,什么时候inserted是多记录呢? 感觉sql中的触发器类似于oracle中的for each row,出现多记录是在什么时候?
如下: insert into tablename1 (field1,field2,...fieldn) select field1,field2,...fieldn from tablename2
难道它只发生了次insert? 我试试
是的,并不是for each row,但有点不可思议.
这样写在oracle的sql下我想可以达到这个需求 declare begin insert into b(item) select item from a where item > 0; insert into c(item) select item from a where item < 0; end;
create or replace trigger TRI_NAME after insert on a for each rowisif :new.item > 0 then ...... elsif :new.item < 0 then ...... end if;end TRI_NAME ;
当然,insert有两种方式\一种是insert into a(item) values(xxxx) 这一种,当然是一条一条的,guo和4no兄的意思是当用了insert into a(item) select item from b 这样的情况,一次insert进去的就不是一条记录这种情况下在Trigger中用 if (select item from inserted) >0是会失败的, 因为select出来的不止是一条记录不会出问题的用法就是象4no兄的一样create trigger xxx on a for insert as insert into b(item) select item from inserted where inserted.item>0 insert into c(item) select item from inserted where inserted.item<0是完全没有问题的,适用于两种状况。p.s. bbssuner的好象不能运行~,我也这么认为。
as
if (select item from inserted)>0 insert into b select item from inserted
else if (select item from inserted)<0 insert into c select item from inserted
on a
for insert
as
select a.itme
...
from inserted
if a.itme >0
begin
insert into b
values(...
...)
end
else
begin
insert into c
values(...
...)
end
asinsert into b select item from inserted where inserted.item>0
insert into c select item from inserted where inserted.item<0return
insert into tablename1 (field1,field2,...fieldn) select field1,field2,...fieldn from tablename2
我试试
declare
begin
insert into b(item)
select item from a
where item > 0;
insert into c(item)
select item from a
where item < 0;
end;
for each rowisif :new.item > 0 then
......
elsif :new.item < 0 then
......
end if;end TRI_NAME ;
这一种,当然是一条一条的,guo和4no兄的意思是当用了insert into a(item)
select item from b
这样的情况,一次insert进去的就不是一条记录这种情况下在Trigger中用 if (select item from inserted) >0是会失败的,
因为select出来的不止是一条记录不会出问题的用法就是象4no兄的一样create trigger xxx on a for insert
as
insert into b(item)
select item from inserted where inserted.item>0
insert into c(item)
select item from inserted where inserted.item<0是完全没有问题的,适用于两种状况。p.s.
bbssuner的好象不能运行~,我也这么认为。