日期:2014-05-17  浏览次数:20857 次

求一个oracle 触发器
有表T1
字段id
code
……

有表t2
字段 
gh(自动增长列)
id
code

现在需要一个触发器
当表t1新增(删除)一条记录时
把所插入记录的id、code插入到表t2中
(删除表t2中相同id的那条记账)



------解决方案--------------------
SQL code
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 c100

DELETE FROM X1 WHERE ID=100;
SELECT * FROM x2;
GH  ID  CODE
2 100 c100
3 100 c100