create or replace trigger tr_trigger_mrzbxx after insert on mrzbxx declare begin update mrzbxx set obj3=obj1||obj2 where obj1 = :old.obj1 and obj2 = :old.obj2; commit; end tr_trigger_mrzbxx;
你的是字段相加还是把两个字符连接起来.create or replace trigger tri_tb before insert on mrzbxx for each row declare pragma autonomous_transaction; begin update mrzbxx set obj3=:new.obj1+:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2; end;
字符相连接update mrzbxx set obj3=:new.obj1||:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2;
以上办法我试过了,还是不行呀。第一种方法提示:OLD,NEW不允许出现在表级触发器中
create or replace trigger tri_tb before insert on mrzbxx for each row begin :new.obj3:=:new.obj1+:new.obj2; end;
create or replace trigger tri_tb before insert on mrzbxx for each row declare pragma autonomous_transaction;--表内自更新要加 begin update mrzbxx set obj3=:new.obj1+:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2; end;
SQL> SQL> CREATE OR REPLACE TRIGGER TR_TEST 2 AFTER INSERT ON TEST 3 FOR EACH ROW 4 DECLARE 5 v_count NUMBER(2); 6 pragma autonomous_transaction; 7 BEGIN 8 9 SELECT COUNT(*) INTO v_count FROM test WHERE a=:new.a; 10 IF v_count>0 THEN 11 UPDATE test SET a=:new.a+1 WHERE a=:new.a; 12 END IF; 13 COMMIT; 14 END TR_TEST; 15 /
after insert on mrzbxx
declare
begin
update mrzbxx set obj3=obj1||obj2 where obj1 = :old.obj1 and obj2 = :old.obj2;
commit;
end tr_trigger_mrzbxx;
declare
pragma autonomous_transaction;
begin
update mrzbxx set obj3=:new.obj1+:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2;
end;
begin
:new.obj3:=:new.obj1+:new.obj2;
end;
declare
pragma autonomous_transaction;--表内自更新要加
begin
update mrzbxx set obj3=:new.obj1+:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2;
end;
还有,我不明白的就是,为什么不在insert语句中写obj3=obj1+obj2?
SQL> DROP TABLE test;
Table dropped
SQL>
SQL> CREATE TABLE test
2 (a NUMBER(2));
Table created
SQL>
SQL> CREATE OR REPLACE TRIGGER TR_TEST
2 AFTER INSERT ON TEST
3 FOR EACH ROW
4 DECLARE
5 v_count NUMBER(2);
6 pragma autonomous_transaction;
7 BEGIN
8
9 SELECT COUNT(*) INTO v_count FROM test WHERE a=:new.a;
10 IF v_count>0 THEN
11 UPDATE test SET a=:new.a+1 WHERE a=:new.a;
12 END IF;
13 COMMIT;
14 END TR_TEST;
15 /
Trigger created
SQL> insert into test values(1);
1 row inserted
SQL> select * from test;
A
---
1
SQL> insert into test values(1);
1 row inserted
SQL> select * from test;
A
---
1
1
SQL> commit;
Commit complete
SQL> insert into test values(1);
1 row inserted
SQL> select * from test;
A
---
2
2
1
SQL> commit;
Commit complete
SQL> select * from test;
A
---
2
2
1
SQL>