CREATE OR REPLACE TRI_A1_INS
AFTER INSERT OR DELETE OR UPDATE ON A1
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO A2 SELECT * FROM :NEW;
END IF;
IF DELETING THEN
DELETE FROM A2 WHERE ID = :OLD.ID ;
END IF;
IF UPDATING THEN
UPATE A2 SET ID = :NEW.ID , NAME = :NEW.NAME WHERE ID = :OLD.ID ;
END IF;
END
我想知道:NEW,:OLD这两个变量是怎么获得的?因为本人才刚始接触oracle。然后能否再顺便说下oracle中的package and package body两者的区别和用途。 THX!
AFTER INSERT OR DELETE OR UPDATE ON A1
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO A2 SELECT * FROM :NEW;
END IF;
IF DELETING THEN
DELETE FROM A2 WHERE ID = :OLD.ID ;
END IF;
IF UPDATING THEN
UPATE A2 SET ID = :NEW.ID , NAME = :NEW.NAME WHERE ID = :OLD.ID ;
END IF;
END
我想知道:NEW,:OLD这两个变量是怎么获得的?因为本人才刚始接触oracle。然后能否再顺便说下oracle中的package and package body两者的区别和用途。 THX!
oracle trigger基本语法
AFTER INSERT OR DELETE OR UPDATE ON A1 --在插入,删除,更新后触发(基于表A1)
FOR EACH ROW --每一行都触发
BEGIN
IF INSERTING THEN --如果是插入(谓词INSERTING如果是插入则为TRUE)
INSERT INTO A2
SELECT :NEW.ID,:NEW.NAME FROM dual; --:new修饰符用来引用列的新值
END IF;
IF DELETING THEN --如果是删除(谓词DELETING如果是插入则为TRUE)
DELETE FROM A2 WHERE ID = :OLD.ID; --:old修饰符用来引用列的旧值
END IF;
IF UPDATING THEN --如果是更新(谓词UPDATING如果是插入则为TRUE)
UPATE A2 SET ID = :NEW.ID, NAME = :NEW.NAME WHERE ID = :OLD.ID;
END IF;
END
/
package定义包头,主要是包的说明部分,全局变量、类型、过程与函数说明。
pageage body定义包体,主要是包的执行部分,定义过程与函数的实现部分。
IF DELETING THEN --如果是删除(谓词DELETING如果是删除则为TRUE)
IF UPDATING THEN --如果是更新(谓词UPDATING如果是更新则为TRUE)
after insert or update or delete on emp
for each row
begin
if INSERTING then
insert into bonus(ENAME,JOB,SAL,COMM,OP)
SELECT :NEW.ENAME,:NEW.JOB,:NEW.SAL,:NEW.COMM,'INSERT' FROM DUAL;
END IF;
IF UPDATING THEN
INSERT INTO BONUS(ENAME,JOB,SAL,COMM,OP)
SELECT :OLD.ENAME,:OLD.JOB,:OLD,SAL,:OLD.COMM,'UPDATE' FROM DUAL;
END IF;
IF DELETING THEN
INSERT INTO BONUS(ENAME,JOB,SAL,COMM,OP)
SELECT :OLD.ENAME,:OLD.JOB,:OLD,SAL,:OLD.COMM,'DELETE' FROM DUAL;
END IF;
end emp_bonus_tri;
那我这个触发器又是哪里有错呢。 我真的找不出来呀。 帮忙看看
这是我在执行delete一条记录的时候报的。。bonus里面没有主键。。它就只有四个字段。