oracle数据库实验4 触发器的使用

改命令提示符set sqlprompt 名字>

scott/密码 登录scott用户
conn sys/ as sysdba切换超级管理员

表格不在一行显示，可修改以下两个参数
set linesize 150
set pagesize 200

1.创建一个触发器，禁止在休息日改变雇员信息。

第一种

create or replace trigger t1
before insert or update or delete on emp
for each row
begin
if to_char(sysdate,'dy')='星期六' or to_char(sysdate,'dy')='星期日' then
raise_application_error(-20001,'禁止在休息日改变雇员信息');
end if;
end;

第二种

create or replace trigger t1
before insert or update or delete on emp
begin
  if to_char(sysdate,'DY') in('星期六','星期日') THEN
    case
      WHEN inserting then
        raise_application_error(-20001,'fail to insert禁止在休息日改变雇员信息');
        WHEN updating THEN
          raise_application_error(-20011,'fail to update禁止在休息日改变雇员信息');
          WHEN deleting THEN
            raise_application_error(-20021,'fail to delete禁止在休息日改变雇员信息');
            END CASE;
       END IF;
    END;

第三种

CREATE OR REPLACE TRIGGER t1
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
    v_day_of_week NUMBER;
BEGIN
    SELECT TO_CHAR(SYSDATE, 'D') INTO v_day_of_week FROM DUAL;
    IF v_day_of_week IN (7, 8) THEN
        RAISE_APPLICATION_ERROR(-20001, '禁止在休息日改变雇员信息.');
    END IF;
END;

 

执行下述代码可以试试触发器效果

insert into emp(empno,ename,sal,deptno) values(12,'Nahida',1111,10);

update emp set sal=6666 where empno=12;

delete from emp where empno=12;

删除触发器以免影响后面的触发器drop trigger t1;

2．为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。

create or replace trigger t2
  after insert or update or delete on scott.emp  
declare
  v_1 number;
  v_2 scott.emp.sal%type;
begin
  if inserting then
    select count(*) into v_1 from scott.emp;
    dbms_output.put_line('执行插入操作后总人数为：'||v_1);
  elsif updating then
    select avg(sal) into v_2 from scott.emp;
    dbms_output.put_line('执行更新操作后平均工资为：'||v_2);
  else
    for v_s in (select deptno,count(*) num from scott.emp group by deptno)
    loop
      dbms_output.put_line('执行删除操作后各个部门的部门号和人数：'||v_s.deptno||' '||v_s.num);
    end loop;
  end if;
end;

执行下述代码可以试试触发器效果

insert into emp(empno,ename,sal,deptno) values(13,'Buer',3333,10); 

update emp set sal=1111 where deptno=10;

delete from emp where deptno=11;

删除触发器以免影响后面的触发器drop trigger t2;

另外，题中“当执行更新工资操作时”好像是要触发器在特定列（工资）更新时执行，

要复杂一点的话我的小脑瓜决定不予考虑  

3．创建一个insert触发器,当在student表中插入一条新记录时，给出‘你已经插入了一条新记录！！！’的提示信息。

create table student
(sno char(6),
sname char(10) not null
);


create or replace trigger t3
after insert  on student
begin
dbms_output.put_line('你已经插入了一条新记录！！！');
end ;

执行下述代码可以试试触发器效果

insert into student values('0001','Buer');

删除触发器以免影响后面的触发器drop trigger t3;

4．创建一个insert触发器,当在student表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。

create or replace trigger t4
before insert  on student
for each row
begin
if :new.sno is null then
raise_application_error(-20004,'不允许出现空值');
end if;
for w in(select sno from student)
loop
if :new.sno=w.sno then
dbms_output.put_line('笨蛋你插入了一条重复记录');
raise_application_error(-20014,'不允许出现重复');
end if;
end loop;
end ;

执行下述代码可以试试触发器效果

insert into student values('0001','Buer');因为题3时执行过，再执行就会重复

insert into student values('','Nahida');学号空

删除触发器以免影响后面的触发器drop trigger t4;

5．创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0----100之间。

create table sc(
sno char(6) not null,
cno char(6) not null,
grade real
);

create table course
(cno char(6),
cname varchar(10)
);



create or replace trigger t5
before insert  on sc
for each row
declare
st number;
ct number;
begin
select count(*) into st from student where sno=:new.sno;
select count(*) into ct from course where cno=:new.cno;
if :new.grade not between 0 and 100 then
raise_application_error(-20005,'成绩超出范围');
end if;
if st=0 or ct=0 then
raise_application_error(-20005,'学号或课程号不存在');
end if;
end ;

执行下述代码可以试试触发器效果

insert into course values('1001','oracle');插入后course和student表都有一行数据了

insert into sc values('0002','1001',60);试试不存在的学号

insert into sc values('0001','1001',120);试试超过范围的成绩

删除触发器以免影响后面的触发器drop trigger t5;

6．创建一个after触发器,在student表中删除某学生的记录时，删除其相应的选课记录。

CREATE OR REPLACE TRIGGER t6
AFTER DELETE ON student
FOR EACH ROW
BEGIN
  DELETE FROM sc WHERE sno = :OLD.sno;
dbms_output.put_line('已删除相应选课记录');
END;

执行下述代码可以试试触发器效果

delete from student where sno='0001';

select * from student;

select * from sc;

删除触发器以免影响后面的触发器drop trigger t6;

7．创建一个instead of 触发器，当在course表中上删出记录时，不允许删除course表中的数据。

一看到错别字我就感觉这题有点问题，因为instead of 仅适用于视图，通常阻止从表中删除数据应该用before delete触发器或其他数据库完整性约束。两种改法：

（1）instead of 改为before

CREATE OR REPLACE TRIGGER t7
BEFORE DELETE ON course
FOR EACH ROW
BEGIN
  RAISE_APPLICATION_ERROR(-20007, '不允许删除course表中的数据');
END;

执行下述代码可以试试触发器效果

insert into course values('1001','oracle');先插入一行

delete from course where cno='1001';试试删除

（2）改为“当在course表中视图上删除记录时”

conn sys as sysdba切换超级管理员

grant create view to scott；授予scott用户创建视图的权限

切换scott用户后创建视图如下：

create view course_view as
select cno,cname from course;

 创建触发器：

create or replace trigger t7

instead of delete on course_view

for each row

begin

raise_application_error(-20007,'不许删除course表数据');
end;

执行下述代码可以试试触发器效果

delete from course_view where cno='1001'; 

 

删除触发器以免影响后面的触发器drop trigger t7;

8．为emp表创建一个触发器，保证修改员工工资时，修改后的工资低于该部门最高工资，同时高于该部门的最低工资

CREATE OR REPLACE TRIGGER t8
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
DECLARE
  max_salary emp.sal%type;
  min_salary emp.sal%type;
  pragma autonomous_transaction;
BEGIN
  SELECT MAX(sal) INTO max_salary
  FROM emp WHERE deptno = :new.deptno;
   SELECT min(sal) INTO min_salary
  FROM emp WHERE deptno = :new.deptno;
  IF :new.sal > max_salary THEN
    RAISE_APPLICATION_ERROR(-20008, '修改的工资高于该部门最高工资');
  elsif :new.sal < min_salary THEN
    RAISE_APPLICATION_ERROR(-20009, '修改的工资低于该部门最低工资');
END IF;
END;

执行下述代码可以试试触发器效果

update emp set sal=6666 where empno=12;最大工资只有5000

select * from emp

强迫症随手删了触发器drop trigger t8;

 

随笔

题2有感：取得当前日期是一个星期中的第几天，星期日为第一天
 select sysdate,to_char(sysdate,'D') from dual; 

取当前日期是星期几，中文显示
select to_char(sysdate,'day') from dual;

Oracle日期常用函数（SYSDATE）象在舞

题4有感：NULL表示不可知不确定，NULL不与任何值相等（包括其本身）

IS NULL 判断某个字符是否为空，并不代表空字符或者是0

=NULL 是判断某个值是否等于NULL

总之，要判断一个数是否等于NULL只能用 IS NULL 或者 IS NOT NULL 来判断

题8有感：报错：表发生了变化，触发器/函数不能读它。加了这句pragma autonomous_transaction后问题解决