给个例子你看一下!!!CREATE OR REPLACE TRIGGER TRIGGER_table AFTER INSERT OR UPDATE OR DELETE ON 表名 FOR EACH ROW DECLARE p_count integer; BEGIN IF INSERTING THEN SELECT COUNT(*) INTO p_count FROM table_name2 WHERE CUSTOMERID = :NEW.CUSTOMERID; IF p_count = 0 THEN INSERT INTO CRM.table_name2 (CUSTOMERID,CUSTOMERCODE,CUSTOMERDESC) VALUES (:NEW.CUSTOMERID,:NEW.CUSTOMERCODE,:NEW.CUSTOMERDESC); END IF; ELSIF UPDATING THEN UPDATE table_name2 SET CUSTOMERID = :NEW.CUSTOMERID, CUSTOMERCODE = :NEW.CUSTOMERCODE, CUSTOMERDESC = :NEW.CUSTOMERDESC WHERE CUSTOMERID = :OLD.CUSTOMERID; ELSE DELETE FROM table_name2 WHERE CUSTOMERID = :OLD.CUSTOMERID; END IF; END;
REFERENCING NEW AS N
语句中定义的,我查了<<Oracle 9i SQL Reference>>,按照其中的定义做的,但是下面INSERT子句使用的时候就会有问题,所以才不知道为什么:)各位高手帮帮忙啊!
user.tbname
user.tbname
CREATE OR REPLACE TRIGGER A_I
BEFORE INSERT ON A_FILE
FOR EACH ROW
INSERT INTO B_FILE values(SUBSTR(:new.A001,1,5),'XXXXX');
INSERT INTO B_FILE SELECT SUBSTR(A001,1,5),'XXXXX' FROM :N
为什么就报错:
TRIGGER A_I 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
1/63 PLS-00049: 错误的赋值变量 'N'警告: 创建的触发器带有编译错误。
触发器中使用new或old,
:new不是一个表,是一行数据
需要这样来使用 :new.colname or :old.colname
只能用
INSERT INTO B_FILE values(SUBSTR(:new.A001,1,5),'XXXXX');
AFTER INSERT OR UPDATE OR DELETE ON 表名 FOR EACH ROW
DECLARE
p_count integer;
BEGIN
IF INSERTING THEN
SELECT COUNT(*) INTO p_count FROM table_name2 WHERE CUSTOMERID = :NEW.CUSTOMERID;
IF p_count = 0 THEN
INSERT INTO CRM.table_name2 (CUSTOMERID,CUSTOMERCODE,CUSTOMERDESC) VALUES (:NEW.CUSTOMERID,:NEW.CUSTOMERCODE,:NEW.CUSTOMERDESC);
END IF;
ELSIF UPDATING THEN
UPDATE table_name2 SET CUSTOMERID = :NEW.CUSTOMERID, CUSTOMERCODE = :NEW.CUSTOMERCODE, CUSTOMERDESC = :NEW.CUSTOMERDESC
WHERE CUSTOMERID = :OLD.CUSTOMERID;
ELSE
DELETE FROM table_name2 WHERE CUSTOMERID = :OLD.CUSTOMERID;
END IF;
END;