执行这句SQL语句老是报错:
create or replace
TRIGGER DRESSUP_UPDATE_TRIGGER
BEFORE INSERT OR DELETE OR UPDATE ON TB_DRESSUPINFO
FOR EACH ROW
DECLARE
V_ENTERID NUMBER(32,0);
pragma autonomous_transaction;
BEGIN
IF UPDATING OR DELETING THEN
SELECT b.enterpriseid INTO V_ENTERID FROM tb_brandinfo b WHERE b.brandid = (SELECT brandorproductid FROM tb_dressupinfo t WHERE t.brandorproductid=:new.brandorproductid);
UPDATE tb_interface_update t SET t.UPDATE_VERSION_NO = t.UPDATE_VERSION_NO+1, t.update_date=sysdate,t.enterpriseid=V_ENTERID WHERE t.update_id=1 AND t.enterpriseid =V_ENTERID;
ELSIF INSERTING THEN
UPDATE tb_interface_update t SET t.UPDATE_VERSION_NO = t.UPDATE_VERSION_NO+1,t.update_date=sysdate,t.enterpriseid=V_ENTERID WHERE t.update_id=1 AND t.enterpriseid =V_ENTERID;
END IF;
COMMIT;
END;在行 1 上开始执行命令时出错:
DELETE FROM tb_dressupinfo WHERE cardressupid=10
错误报告:
SQL 错误: ORA-01403: 未找到任何数据
ORA-06512: 在 "ADMIN.DRESSUP_UPDATE_TRIGGER", line 6
ORA-04088: 触发器 'ADMIN.DRESSUP_UPDATE_TRIGGER' 执行过程中出错
01403. 00000 - "no data found"
*Cause:
*Action:
create or replace
TRIGGER DRESSUP_UPDATE_TRIGGER
BEFORE INSERT OR DELETE OR UPDATE ON TB_DRESSUPINFO
FOR EACH ROW
DECLARE
V_ENTERID NUMBER(32,0);
pragma autonomous_transaction;
BEGIN
IF UPDATING OR DELETING THEN
SELECT b.enterpriseid INTO V_ENTERID FROM tb_brandinfo b WHERE b.brandid = (SELECT brandorproductid FROM tb_dressupinfo t WHERE t.brandorproductid=:new.brandorproductid);
UPDATE tb_interface_update t SET t.UPDATE_VERSION_NO = t.UPDATE_VERSION_NO+1, t.update_date=sysdate,t.enterpriseid=V_ENTERID WHERE t.update_id=1 AND t.enterpriseid =V_ENTERID;
ELSIF INSERTING THEN
UPDATE tb_interface_update t SET t.UPDATE_VERSION_NO = t.UPDATE_VERSION_NO+1,t.update_date=sysdate,t.enterpriseid=V_ENTERID WHERE t.update_id=1 AND t.enterpriseid =V_ENTERID;
END IF;
COMMIT;
END;在行 1 上开始执行命令时出错:
DELETE FROM tb_dressupinfo WHERE cardressupid=10
错误报告:
SQL 错误: ORA-01403: 未找到任何数据
ORA-06512: 在 "ADMIN.DRESSUP_UPDATE_TRIGGER", line 6
ORA-04088: 触发器 'ADMIN.DRESSUP_UPDATE_TRIGGER' 执行过程中出错
01403. 00000 - "no data found"
*Cause:
*Action:
before insert or delete or update on A
for each row
declare
-- local variables here
begin
update B set NO=NO+1 where enterpriseId=:new.enterpiseId;
end tri_1;
根据你的错误提示很明显是在作删除操作触发了:
在行 1 上开始执行命令时出错:
DELETE FROM tb_dressupinfo WHERE cardressupid=10
而删除操作时:new.brandorproductid是不存在的,可以使用:old.brandorproductid
还有一个问题是,在触发器里面不能commit;
另外就是你的更新、删除、插入操作时的更新语句是一样的啊看你写的触发器的功能可以换成这样的:create or replace
TRIGGER DRESSUP_UPDATE_TRIGGER
BEFORE INSERT OR DELETE OR UPDATE ON TB_DRESSUPINFO
FOR EACH ROW
DECLARE
V_ENTERID NUMBER(32,0);
pragma autonomous_transaction;
BEGINSELECT b.enterpriseid INTO V_ENTERID FROM tb_brandinfo b WHERE b.brandid=:old.brandorproductid;UPDATE tb_interface_update t SET t.UPDATE_VERSION_NO = t.UPDATE_VERSION_NO+1,t.update_date=sysdate,t.enterpriseid=V_ENTERID WHERE t.update_id=1 AND t.enterpriseid =V_ENTERID;
END;
TRIGGER DRESSUP_UPDATE_TRIGGER
BEFORE INSERT OR DELETE OR UPDATE ON TB_DRESSUPINFO
FOR EACH ROW
DECLARE
ENTERID NUMBER(32,0);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT b.enterpriseid INTO ENTERID FROM tb_brandinfo b WHERE b.brandid = (SELECT DISTINCT(t.brandorproductid) FROM tb_dressupinfo t WHERE t.brandorproductid=:old.brandorproductid);
UPDATE tb_interface_update t SET t.UPDATE_VERSION_NO = t.UPDATE_VERSION_NO+1, t.update_date=sysdate,t.enterpriseid=ENTERID WHERE t.update_id=1 AND t.enterpriseid =ENTERID;
END;我写的这个根据你的修改的,但是还是出现错误。
在我想tb_dressupinfo 中INSERT数据时,
出现
=============
保存对表 "ADMIN"."TB_DRESSUPINFO" 的更改时出现一个错误:
行 9: ORA-01403: 未找到任何数据
ORA-01403: 未找到任何数据
ORA-06512: 在 "ADMIN.DRESSUP_UPDATE_TRIGGER", line 5
ORA-04088: 触发器 'ADMIN.DRESSUP_UPDATE_TRIGGER' 执行过程中出错
ORA-06512: 在 line 1
SELECT enterpriseid FROM tb_brandinfo WHERE brandid = (SELECT distinct(tb_dressupinfo.brandorproductid) FROM tb_dressupinfo where tb_dressupinfo.brandorproductid = 1)执行这个语句是有结果的。
create or replace trigger tri_1
before insert or delete or update on A
for each row
declare
pragma autonomous_transaction;
begin
update B set num=num+1 where B_TID=:new.tid;
commit;
end;