1. instead of trigger 是基于視圖建立的,不能建在表上,為什么要建在視圖上,一般的視圖如果其數(shù)據(jù)來(lái)源一個(gè)表并且包含該表的主鍵,就可以對(duì)視圖進(jìn)行DML操作.另外一種情況是從多個(gè)表查詢出來(lái)的.這樣我們就不能對(duì)視圖進(jìn)行操作了,也就是只能查詢.instead of trigger可以解決建在多表上視圖的更新操作.
2.下面我們就來(lái)實(shí)例操作:
a.先建表,簡(jiǎn)單點(diǎn)就三個(gè)分別是學(xué)生表,課程表,學(xué)生選課表
CREATE TABLE STUDENT
(
   CODE  VARCHAR2(5),
   LNAME VARCHAR2(200)
)
CREATE TABLE COURSE
(
    CODE  VARCHAR2(5),
    CNAME VARCHAR2(30)
)
CREATE TABLE ST_CR
(
   STUDENT VARCHAR2(5),
   COURSE  VARCHAR2(5),
   GRADE   NUMBER
)
--表的約束
ALTER TABLE STUDENT ADD CONSTRAINT STUDENT$PK PRIMARY KEY(CODE);
ALTER TABLE COURSE ADD CONSTRAINT COURSE$PK PRIMARY KEY(CODE);
ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$PK PRIMARY KEY(STUDENT, COURSE);
ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$FK$STUDENT FOREIGN KEY(STUDENT) REFERENCES STUDENT(CODE);
ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$FK$COURSE FOREIGN KEY(COURSE) REFERENCES COURSE(CODE);

b.基于這三個(gè)表的視圖
CREATE OR REPLACE VIEW STUDENT_STATUS AS
   SELECT S.CODE S_CODE, S.LNAME STUDENT, C.CODE C_CODE, C.CNAME COURSE, SC.GRADE GRADE
      FROM STUDENT S, COURSE C, ST_CR SC
        WHERE S.CODE = SC.STUDENT
          AND C.CODE = SC.COURSE

c.基于視圖的觸發(fā)器
CREATE OR REPLACE TRIGGER TRI_STCR INSTEAD OF INSERT ON STUDENT_STATUS
   FOR EACH ROW
DECLARE
  W_ACTION VARCHAR2(1);
BEGIN
  IF    INSERTING THEN
    W_ACTION := 'I';
  ELSE
    RAISE PROGRAM_ERROR;
  END IF;
    INSERT INTO STUDENT(CODE, LNAME) VALUES(:NEW.S_CODE,:NEW.STUDENT);
    INSERT INTO COURSE(CODE, CNAME) VALUES(:NEW.C_CODE, :NEW.COURSE);
    INSERT INTO ST_CR(STUDENT, COURSE, GRADE)
       VALUES(:NEW.S_CODE, :NEW.C_CODE, :NEW.GRADE);
END;

d.對(duì)視圖執(zhí)行數(shù)據(jù)插入
INSERT INTO STUDENT_STATUS(S_CODE, STUDENT, C_CODE, COURSE, GRADE)
       VALUES('001','Mike','EN','English',86);

可以看到每個(gè)表各有一條數(shù)據(jù)已經(jīng)插入.