1. instead of trigger 是基于视图建立的,不能建在表上,为什么要建在视图上,一般的视图如果其数据来源一个表并且包含该表的主键,就可以对视图进行DML操作.另外一种情况是从多个表查询出来的.这样我们就不能对视图进行操作了,也就是只能查询.instead of trigger可以解决建在多表上视图的更新操作.
2.下面我们就来实例操作:
a.先建表,简单点就三个分别是学生表,课程表,学生选课表
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.基于这三个表的视图
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.基于视图的触发器
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.对视图执行数据插入
INSERT INTO STUDENT_STATUS(S_CODE, STUDENT, C_CODE, COURSE, GRADE)
VALUES('001','Mike','EN','English',86);
可以看到每个表各有一条数据已经插入.
posted on 2009-10-14 17:51
谭明 阅读(5150)
评论(2) 编辑 收藏 所属分类:
Oracle