CREATE OR REPLACE TRIGGER insert_to_B AFTER INSERT ON A REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE v_data VARCHAR2 (100) := NULL; BEGIN INSERT INTO B (ID, data1, data2 ) VALUES (:NEW.ID, v_data, :NEW.data2 ); END;
CREATE OR REPLACE TRIGGER insert_to_A BEFORE INSERT ON A REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF :NEW.data1 IS NOT NULL THEN :NEW.data1 := NULL; END IF; END;
第一个trigger改下: CREATE OR REPLACE TRIGGER insert_to_B AFTER INSERT ON A REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO B (ID, data1, data2 ) VALUES (:NEW.ID, :NEW.data1, :NEW.data2 ); END;
SQL> create table tba(date1 number,date2 number) 2 /表已创建。SQL> create table tbb(date1 number,date2 number) 2 /表已创建。SQL> create or replace trigger tri_tbb before insert on tba for each row 2 begin 3 insert into tbb values(:new.date1,:new.date2); 4 :new.date1:=null; 5 end; 6 /触发器已创建SQL> insert into tba values(1,2);已创建 1 行。SQL> commit 2 /提交完成。SQL> select * from tba 2 / DATE1 DATE2 ---------- ---------- 2SQL> select * from tbb 2 / DATE1 DATE2 ---------- ---------- 1 2
是不是要创建二个触发器才行呢?一个BEFORE 和一个 AFTER 才行呢? 4楼那样创建一个AFTER不行吧,我要的是插入的 A表 data1字段的值 如果不是NULL 的话,就改成NULL再插入A表中 ,这个是在插入前就得处理了,
4楼的应该可以.. 把问题复杂化了.. CREATE OR REPLACE TRIGGER insert_to_A BEFORE INSERT ON A REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :NEW.data1 := NULL; INSERT INTO b (ID, data1, data2 ) VALUES (:NEW.ID, NULL, :NEW.data2 ); END;
--这样就行了啊!简单点 CREATE OR REPLACE TRIGGER trg_a BEFORE INSERT ON a FOR EACH ROW BEGIN :NEW.data1:=NULL; INSERT INTO b VALUES(:NEW.id,:NEW.data1,:NEW.data2); END;
AFTER INSERT
ON A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_data VARCHAR2 (100) := NULL;
BEGIN
INSERT INTO B
(ID, data1, data2
)
VALUES (:NEW.ID, v_data, :NEW.data2
);
END;
BEFORE INSERT
ON A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.data1 IS NOT NULL
THEN
:NEW.data1 := NULL;
END IF;
END;
CREATE OR REPLACE TRIGGER insert_to_B
AFTER INSERT
ON A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO B
(ID, data1, data2
)
VALUES (:NEW.ID, :NEW.data1, :NEW.data2
);
END;
SQL> create table tba(date1 number,date2 number)
2 /表已创建。SQL> create table tbb(date1 number,date2 number)
2 /表已创建。SQL> create or replace trigger tri_tbb before insert on tba for each row
2 begin
3 insert into tbb values(:new.date1,:new.date2);
4 :new.date1:=null;
5 end;
6 /触发器已创建SQL> insert into tba values(1,2);已创建 1 行。SQL> commit
2 /提交完成。SQL> select * from tba
2 / DATE1 DATE2
---------- ----------
2SQL> select * from tbb
2 / DATE1 DATE2
---------- ----------
1 2
4楼那样创建一个AFTER不行吧,我要的是插入的 A表 data1字段的值 如果不是NULL 的话,就改成NULL再插入A表中 ,这个是在插入前就得处理了,
CREATE OR REPLACE TRIGGER insert_to_A
BEFORE INSERT
ON A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:NEW.data1 := NULL; INSERT INTO b
(ID, data1, data2
)
VALUES (:NEW.ID, NULL, :NEW.data2
);
END;
--这样就行了啊!简单点
CREATE OR REPLACE TRIGGER trg_a
BEFORE INSERT ON a
FOR EACH ROW
BEGIN
:NEW.data1:=NULL;
INSERT INTO b VALUES(:NEW.id,:NEW.data1,:NEW.data2);
END;