触发器、视图

--1、触发器-------------drop trigger myTrigger
CREATE OR REPLACE TRIGGER myTrigger
BEFORE  DELETE 
ON grade
BEGIN   
  dbms_output.put_line('为自己的删除负责！！');

END ;

SELECT * FROM grade;

DELETE FROM grade WHERE ID=4; 

INSERT INTO grade VALUES(4,'四年级');
----2、创建触发器，删除班级表信息之前，先把Student表中相关引用的数据删除-----drop trigger myTrigger2
CREATE OR REPLACE TRIGGER myTrigger2
BEFORE  DELETE 
ON grade
FOR EACH ROW
BEGIN   
  DELETE FROM Student WHERE gradeId= :old.id; 
END ;

DELETE FROM grade WHERE ID=3;

SELECT * FROM grade;

SELECT * FROM Student;

DELETE FROM grade WHERE ID=1;
-----3、当插入一条年级信息时时候，自动分配一个学生到该年级-----drop trigger myTrigger3
CREATE OR REPLACE TRIGGER myTrigger3
AFTER INSERT
ON grade
FOR EACH ROW
BEGIN
  INSERT INTO Student VALUES(:new.id||0,'默认学生'，:new.id,'中国','110110110','123@qq.com','男'，to_date('1949-10-01','yyyy-mm-dd'));
END;

SELECT * FROM student;

INSERT INTO grade VALUES (8,'五年级');

SELECT * FROM grade
-----4、给SMITH调薪，如果比原来的还要少，输出一句  我不干了！！！ ---------?????-------drop trigger myTrigger5
CREATE OR REPLACE TRIGGER myTrigger5
AFTER UPDATE
ON zhang111
FOR EACH ROW
BEGIN
  IF(:old.sal>:new.sal)THEN 
    dbms_output.put_line('我不干了！！！！');
  END IF;
END;

SELECT * FROM zhang111;

UPDATE zhang111 SET sal=100 WHERE ename='SMITH';

CREATE TABLE zhang111 AS SELECT * FROM scott.emp;
-----5、创建一个视图，查询等级比FORD高的员工信息-----------drop view myView
CREATE OR REPLACE VIEW myView AS 
SELECT * FROM scott.emp 
WHERE sal>(SELECT hisal FROM scott.emp,scott.salgrade WHERE ename='FORD'AND sal BETWEEN losal AND hisal);

SELECT * FROM myView;
---------------连表查询时，视图不可以修改
---------------当有group by 和聚合函数的时候不允许更改数据
---------------当有集合运算符时，不允许更改数据