update B set b1=(select sum(a1) from A where a3=1), b2=(select count(*) from A)
create or replace trigger test after update on for each row declare num number(5); num2 number(5); begin select count(*) into num from A;
select sum(a1) into num2 from A where 1=1;
insert into B(b1,b2) values(num,num2); end test;
create or replace trigger test after update on for each row declare num number(5); num2 number(5); begin select count(*) into num from A;
select sum(a1) into num2 from A where 1=1;
insert into B(b1,b2) values(num,num2); end test;
不好意思,没看到最后一个条件。create or replace trigger trigger_test after update on A for each row declare num number(5); num2 number(5); begin select count(*) into num from A;
select sum(a1) into num2 from A where a3=1;
insert into B(b1,b2) values(num,num2); end test;
错了,不好意思,错了,最后时更新B表的b1,b2楼上的方式我试过了,但总时报错,在select行。
CREATE OR REPLACE TRIGGER trig_t AFTER UPDATE ON a DECLARE cc NUMBER(5); sc NUMBER(5); BEGIN SELECT COUNT(*) INTO cc FROM a; SELECT SUM(ID) INTO NUM2 FROM a WHERE a3 = 1; UPDATE b3 SET b1=sc,b2=cc WHERE 你更新的关联条件; END; /只能用表级触发器,不能用行级触发器.
create or replace trigger TestT before insert or update on A for each row declare sumPrice1 number(10); sumPrice2 number(10); begin select sum(a1) into sumPrice1 from A where a3='1' ; select b1 into sumPrice2 from B where Bid='001' sumPrice2:=sumPrice1; update B set b1=sumPrice2 where Bid='001'; end TestT ;这是我写的更新一个值的代码,总在第一个SELECT语句出报错,为什么呢
因为这里不能用行级触发器,去掉for each row 就ok了。
试了一下,确实去掉“for each row ”就可以了。 如果不去掉这一句,触发器和执行语句将会陷入一个死循环,无法执行的
b2=(select count(*) from A)
create or replace trigger test
after update on
for each row
declare
num number(5);
num2 number(5);
begin
select count(*)
into num
from A;
select sum(a1)
into num2
from A
where 1=1;
insert into B(b1,b2)
values(num,num2);
end test;
create or replace trigger test
after update on
for each row
declare
num number(5);
num2 number(5);
begin
select count(*)
into num
from A;
select sum(a1)
into num2
from A
where 1=1;
insert into B(b1,b2)
values(num,num2);
end test;
after update on A
for each row
declare
num number(5);
num2 number(5);
begin
select count(*)
into num
from A;
select sum(a1)
into num2
from A
where a3=1;
insert into B(b1,b2)
values(num,num2);
end test;
错了,不好意思,错了,最后时更新B表的b1,b2楼上的方式我试过了,但总时报错,在select行。
CREATE OR REPLACE TRIGGER trig_t
AFTER UPDATE ON a
DECLARE
cc NUMBER(5);
sc NUMBER(5);
BEGIN
SELECT COUNT(*) INTO cc FROM a;
SELECT SUM(ID) INTO NUM2 FROM a WHERE a3 = 1;
UPDATE b3 SET b1=sc,b2=cc WHERE 你更新的关联条件;
END;
/只能用表级触发器,不能用行级触发器.
before insert or update on A for each row
declare sumPrice1 number(10);
sumPrice2 number(10);
begin select sum(a1) into sumPrice1 from A where a3='1' ;
select b1 into sumPrice2 from B where Bid='001' sumPrice2:=sumPrice1; update B set b1=sumPrice2 where Bid='001'; end TestT ;这是我写的更新一个值的代码,总在第一个SELECT语句出报错,为什么呢
如果不去掉这一句,触发器和执行语句将会陷入一个死循环,无法执行的