oracle級聯刪除 觸發器，oracle級觸發器

oracle級聯刪除 觸發器，oracle級觸發器

CREATE TABLE STUDENT( --創建學生表
  ID NUMBER(10) PRIMARY KEY,   --主鍵ID
  SNAME VARCHAR2(20),
  CLASSNAME VARCHAR2(20) --班級ID

);

INSERT INTO STUDENT VALUES(1,'Tom',1);
INSERT INTO STUDENT VALUES(2,'Jack',1);
INSERT INTO STUDENT VALUES(3,'Bay',2);
INSERT INTO STUDENT VALUES(4,'John',3);

CREATE TABLE CLASSTAB(  --創建班級表
  CLASSID NUMBER(2) PRIMARY KEY,  --班級ID
  CNAME VARCHAR2(20)
);

INSERT INTO CLASSTAB VALUES(1,'3G');
INSERT INTO CLASSTAB VALUES(2,'SVSE');
INSERT INTO CLASSTAB VALUES(3,'GIS');
INSERT INTO CLASSTAB VALUES(4,'EM');

--創建觸發器  刪除班級時  將該班級的所有學生信息也刪除
CREATE OR REPLACE TRIGGER MYTRIGGER
  AFTER DELETE 
  ON CLASSTAB
  FOR EACH ROW
BEGIN
  DELETE FROM STUDENT WHERE CLASSNAME = :old.CLASSID;
END;

DELETE FROM CLASSTAB WHERE CLASSID = 2;  刪除班級表中CLASSID為2的班級  學生表的第三條記錄也會被刪除