有钢管表,有三个字段如:钢管号 钢管长度 钢管重量要求写一个触发器,在输入钢管号及钢管长度时,自动将相应行的钢管重量设置为 钢管长度*10我写的触发器如:
CREATE OR REPLACE TRIGGER GMAN.GANGGUAN
AFTER INSERT
ON GMAN.钢管表
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE 钢管表
SET 钢管重量= 钢管长度*10
WHERE 钢管号 = :new.钢管号;
END;但是在执行过程中,却提示出错,如:
ORA-04091: 表 GMAN.钢管表 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "GMAN.GANGGUAN", line 2
ORA-04088: 触发器 GMAN.GANGGUAN 执行过程中出错求高手帮帮忙,急
AFTER INSERT
ON GMAN.钢管表
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE 钢管表
SET 钢管重量= 钢管长度*10
WHERE 钢管号 = :new.钢管号;
END;
CREATE OR REPLACE TRIGGER GMAN.GANGGUAN
AFTER INSERT
ON GMAN.钢管表
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:new.钢管重量= 钢管长度*10
END;
ORA-06512: 在 "ROLL.TEST", line 7
ORA-04088: 触发器 'ROLL.TEST' 执行过程中出错
:new.钢管重量= 钢管长度*10;
ORA-06512: 在 "GMAN.GANGGUAN", line 7
ORA-04088: 触发器 GMAN.GANGGUAN 执行过程中出错
AFTER INSERT
ON GMAN.钢管表
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE 钢管表
SET 钢管重量= 钢管长度*10
WHERE 钢管号 = :new.钢管号;
commit;
END;显式提交,在运行前,先提交前面未提交/回滚的自治事务。
06519, 00000, "active autonomous transaction detected and rolled back"
// *Cause: Before returning from an autonomous PL/SQL block, all autonomous
// transactions started within the block must be completed (either
// committed or rolled back). If not, the active autonomous
// transaction is implicitly rolled back and this error is raised.
// *Action: Ensure that before returning from an autonomous PL/SQL block,
// any active autonomous transactions are explicitly committed
// or rolled back.
使用before触发器,请看效果。
SQL> create TABLE 钢管表
2 (
3 钢管号 number,
4 钢管长度 number,
5 钢管重量 number
6 )
7 ;表已创建。SQL> CREATE OR REPLACE TRIGGER GANGGUAN
2 before INSERT
3 ON 钢管表
4 REFERENCING NEW AS NEW OLD AS OLD
5 FOR EACH ROW
6 DECLARE
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 :new.钢管重量 := :new.钢管长度*10;
10 END;
11 /触发器已创建SQL> select * from 钢管表;未选定行SQL> insert into 钢管表 (钢管号,钢管长度) values (1,7.77);已创建 1 行。SQL> commit;提交完成。SQL> select * from 钢管表; 钢管号 钢管长度 钢管重量
---------- ---------- ----------
1 7.77 77.7SQL>
2 (
3 钢管号 number,
4 钢管长度 number,
5 钢管重量 number
6 );表已创建。SQL> CREATE OR REPLACE TRIGGER GANGGUAN
2 before INSERT
3 ON 钢管表
4 REFERENCING NEW AS NEW OLD AS OLD
5 FOR EACH ROW
6 DECLARE
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 :new.钢管重量 := :new.钢管长度*10;
10 END;
11 /触发器已创建SQL> select * from 钢管表;未选定行SQL> insert into 钢管表 (钢管号,钢管长度) values (1,7.77);已创建 1 行。SQL> commit;提交完成。SQL> select * from 钢管表; 钢管号 钢管长度 钢管重量
---------- ---------- ----------
1 7.77 77.7SQL> insert into 钢管表 (钢管号,钢管长度) values (1,8.88);已创建 1 行。SQL> commit;提交完成。SQL> select * from 钢管表; 钢管号 钢管长度 钢管重量
---------- ---------- ----------
1 7.77 77.7
1 8.88 88.8SQL> insert into 钢管表 (钢管号,钢管长度) values (3,9.99);已创建 1 行。SQL> commit;提交完成。SQL> select * from 钢管表; 钢管号 钢管长度 钢管重量
---------- ---------- ----------
1 7.77 77.7
1 8.88 88.8
3 9.99 99.9SQL>
不会吧。。
问题已解决,谢谢suiziguo,谢谢大家!
Select * From gg;然后再基于这个视图做一个触发器,Create Or Replace Trigger tri
Instead Of Insert On ggv
Referencing New As New Old as Old
For Each Row
Begin
Insert Into gg Values(:new.Ggid,:New.Ggcd,:New.Ggcd*10);
End;然后插入数据时,向这个视图插入,Insert Into ggv(ggid,ggcd) values (3,30);这样??!