17,377
社区成员
发帖
与我相关
我的任务
分享★ 高分求解：触发器问题. ★
创建触发器：
CREATE OR REPLACE TRIGGER bearing_state_update_trig
AFTER UPDATE OF state ON res_bearing
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
BEGIN
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
UPDATE res_bearing
SET res_bearing.state = 99,
res_bearing.scrap_date = sysdate
WHERE res_bearing.bearing_id = :new_res_bearing.bearing_id;
END IF;
END;
/
触发器已创建。
UPDATE res_bearing SET state=99 where bearing_id='121-002';
执行SQL语句报错如下！
ORA-04091: 表 SYSTEM.RES_BEARING ·发生变化触发器/函数不能读。
ORA-06512: 在"SYSTEM.BEARING_STATE_UPDATE_TRIG", line 4
ORA-04088: 触发器 'SYSTEM.BEARING_STATE_UPDATE_TRIG' 执行过程中出错
解决马上结帖！
CREATE OR REPLACE TRIGGER bearing_state_update_trig
AFTER UPDATE OF state ON res_bearing
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
BEGIN
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
UPDATE res_bearing
SET res_bearing.state = 99,
res_bearing.scrap_date = sysdate
WHERE res_bearing.bearing_id = :new_res_bearing.bearing_id;
END IF;
END;
/
触发器已创建。
UPDATE res_bearing SET state=99 where bearing_id='121-002';
执行SQL语句报错如下！
ORA-04091: 表 SYSTEM.RES_BEARING ·发生变化触发器/函数不能读。
ORA-06512: 在"SYSTEM.BEARING_STATE_UPDATE_TRIG", line 4
ORA-04088: 触发器 'SYSTEM.BEARING_STATE_UPDATE_TRIG' 执行过程中出错
解决马上结帖！
...全文
请发表友善的回复…
发表回复
sydt2000 2003-03-04
- 打赏
- 举报
多谢各位！
结贴！
结贴！
sydt2000 2003-03-04
- 打赏
- 举报
To: txchinali(小虫)
多谢！
多谢！
txchinali 2003-03-03
- 打赏
- 举报
AFTER UPDATE OF state 是在update后进行触发器操作
BEFORE UPDATE OF state 是在before之前进行触发器操作
主要取觉于用于在update前操作还是update后操作
BEFORE UPDATE OF state 是在before之前进行触发器操作
主要取觉于用于在update前操作还是update后操作
sydt2000 2003-03-03
- 打赏
- 举报
To: Lastdrop(空杯)
多谢！
多谢！
Lastdrop 2003-03-03
- 打赏
- 举报
AFTER UPDATE OF state 改为BEFORE UPDATE OF state
sydt2000 2003-03-03
- 打赏
- 举报
To: beckhambobo(beckham)
去掉此句:new_res_bearing.state := 99;
ORA-04084: 无法更改此触发器类型的NEW值
还是出现上述错误！
去掉此句:new_res_bearing.state := 99;
ORA-04084: 无法更改此触发器类型的NEW值
还是出现上述错误！
beckhambobo 2003-03-03
- 打赏
- 举报
sorry,去掉此句
:new_res_bearing.state := 99;
:new_res_bearing.state := 99;
sydt2000 2003-03-03
- 打赏
- 举报
To: beckhambobo(beckham)
ORA-04084: 无法更改此触发器类型的NEW值
出现上述错误！
ORA-04084: 无法更改此触发器类型的NEW值
出现上述错误！
beckhambobo 2003-03-03
- 打赏
- 举报
去掉后，任何一列被更新时都会触发该触发器.
sydt2000 2003-03-03
- 打赏
- 举报
To: beckhambobo(beckham)
CREATE OR REPLACE TRIGGER bearing_state_update_trig
AFTER UPDATE OF state ON res_bearing -- 只在state字段发生改变时触发，为什么
-- 去掉OF state ？？
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
...
拜托解释一下？
CREATE OR REPLACE TRIGGER bearing_state_update_trig
AFTER UPDATE OF state ON res_bearing -- 只在state字段发生改变时触发，为什么
-- 去掉OF state ？？
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
...
拜托解释一下？
beckhambobo 2003-03-03
- 打赏
- 举报
before UPDATE OF state ON res_bearing
修改为:
before UPDATE ON res_bearing
修改为:
before UPDATE ON res_bearing
sydt2000 2003-03-03
- 打赏
- 举报
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
:new_res_bearing.state := 99;
:new_res_bearing.scrap_date := sysdate;
END IF;
ORA-04084: 无法更改此触发器类型的NEW值
:new_res_bearing.state := 99;
:new_res_bearing.scrap_date := sysdate;
END IF;
ORA-04084: 无法更改此触发器类型的NEW值
sydt2000 2003-03-03
- 打赏
- 举报
ORA-04084: 无法更改次触发器类型的NEW值
vongates968 2003-03-03
- 打赏
- 举报
up
beckhambobo 2003-03-03
- 打赏
- 举报
不用了，:new记录更新当前数据，具体到那条数据，触发器会做的，不用再次判定.
sydt2000 2003-03-03
- 打赏
- 举报
二位大虾！
小弟菜！
在触发器中不需进行条件判断么？bearing_id字段为表的主关键字？
能否帮解释一下？
小弟菜！
在触发器中不需进行条件判断么？bearing_id字段为表的主关键字？
能否帮解释一下？
beckhambobo 2003-03-03
- 打赏
- 举报
CREATE OR REPLACE TRIGGER bearing_state_update_trig
before UPDATE OF state ON res_bearing
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
BEGIN
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
:new_res_bearing.state:= 99;
:new_res_bearing.scrap_date := sysdate;
END IF;
END;
/
before UPDATE OF state ON res_bearing
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
BEGIN
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
:new_res_bearing.state:= 99;
:new_res_bearing.scrap_date := sysdate;
END IF;
END;
/
bobfang 2003-03-03
- 打赏
- 举报
CREATE OR REPLACE TRIGGER bearing_state_update_trig
before UPDATE OF state ON res_bearing
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
BEGIN
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
:new_res_bearing.scrap_date := sysdate;
END IF;
END;
/
before UPDATE OF state ON res_bearing
REFERENCING NEW AS new_res_bearing OLD AS old_res_bearing
FOR EACH ROW
BEGIN
IF ( :new_res_bearing.state = 99 ) THEN -- 轴承报废
:new_res_bearing.scrap_date := sysdate;
END IF;
END;
/
