CREATE TABLE X1 (id NUMBER(5),code VARCHAR2(10)); CREATE TABLE X2 (gh NUMBER(5),id NUMBER(5),code VARCHAR2(10));CREATE SEQUENCE seq_x2 MINVALUE 1 MAXVALUE 99999 START WITH 1 INCREMENT BY 1 NOCACHE; SELECT seq_x2.NEXTVAL FROM dual; SELECT seq_x2.CURRVAL FROM dual;CREATE OR REPLACE TRIGGER xx1 AFTER INSERT OR DELETE ON x1 FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO x2(gh,id,code) VALUES (seq_x2.NEXTVAL,:NEW.id,:NEW.code); END IF; IF DELETING THEN INSERT INTO x2(gh,id,code) VALUES (seq_x2.NEXTVAL,:old.id,:old.code); END IF; END xx1;INSERT INTO x1 VALUES (100,'c100'); SELECT * FROM x2; GH ID CODE 2 100 c100DELETE FROM X1 WHERE ID=100; SELECT * FROM x2; GH ID CODE 2 100 c100 3 100 c100
是指t2插入新纪录的时候要先删除Id相同的记录吗?
CREATE TABLE X2 (gh NUMBER(5),id NUMBER(5),code VARCHAR2(10));CREATE SEQUENCE seq_x2
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1
NOCACHE;
SELECT seq_x2.NEXTVAL FROM dual;
SELECT seq_x2.CURRVAL FROM dual;CREATE OR REPLACE TRIGGER xx1
AFTER INSERT OR DELETE ON x1 FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO x2(gh,id,code) VALUES (seq_x2.NEXTVAL,:NEW.id,:NEW.code);
END IF; IF DELETING THEN
INSERT INTO x2(gh,id,code) VALUES (seq_x2.NEXTVAL,:old.id,:old.code);
END IF;
END xx1;INSERT INTO x1 VALUES (100,'c100');
SELECT * FROM x2;
GH ID CODE
2 100 c100DELETE FROM X1 WHERE ID=100;
SELECT * FROM x2;
GH ID CODE
2 100 c100
3 100 c100