【Oracle数据库】实验-触发器trigger

触发器和存储过程的区别

1. procedure 带参数，trigger不带参数。
2. procedure 体将declare 改为is，trigger体局部变量的声明依然是declare。
3. procedure 的调用采用程序块主动调用，trigger是对on之后的对象做了某种动作（如insert）才触发。
4. trigger追加了时间，时间，对象，方式等的细节。

实验题

1、增加一新表XS_1，表结构和表XS相同，用来存放从XS表中删除的记录。
触发器tr_1

create or replace trigger tr_l
  before delete on xs
  for each row
declare
 
begin
  insert into xs_del(xh,xm,zym,xb,cssj,zxf)
  values(:old.xh,:old.xm,:old.zym,:old.xb,:old.cssj,:old.zxf);
end tr_l;

操作过程：
（1）建xs_del表

create table xs_del as select * from xs;
truncate table xs_del;

（2）查询xs表

（3）执行一条删除语句

delete from xs where xh='001';

（4）查询xs_del表

2、监控用户对XS表的操作，要求：当XS表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间。
触发器tr_2：

create or replace trigger tr_2
  after delete or insert or update on xs
  for each row
declare
   v_info sql_info.info%type;
begin
  if inserting then
      v_info:='插入';
    elsif updating then
      v_info:='更新';
    else
      v_info:='删除';
  end if;
  insert into sql_info values (v_info,sysdate);
end tr_2;

操作过程：
（1）创建sql_info表

create table sql_info(info varchar2(10),time date);

（2）手动更新一个数据
eg.更新张琼同学为张小琼同学

(3)查询sql_info表

改进：综合前两个例子
触发器tr_3：

create or replace trigger tr_3
  after delete or insert or update on xs
  for each row
declare
   v_info sql_info.info%type;
begin
  if inserting then
      v_info:='插入';
      insert into zqy(time,info,xh,xm,zym,xb,cssj,zxf,bz) values(sysdate,v_info,:new.xh,:new.xm,:new.zym,:new.xb,:new.cssj,:new.zxf,:new.bz);
    elsif updating then
      v_info:='更新';
      insert into zqy(time,info,xh,xm,zym,xb,cssj,zxf,bz) values(sysdate,v_info,:old.xh,:old.xm,:old.zym,:old.xb,:old.cssj,:old.zxf,:old.bz);
    else
      v_info:='删除';
      insert into zqy(time,info,xh,xm,zym,xb,cssj,zxf,bz) values(sysdate,v_info,:old.xh,:old.xm,:old.zym,:old.xb,:old.cssj,:old.zxf,:old.bz);
  end if;
end tr_3;

3、针对Scott.emp表，记录其相应操作的信息，具体如下:
当执行插入操作时，统计操作后员工人数；
当执行更新工资操作时，统计更新后员工平均工资；
当执行删除操作时，统计删除后各个部门剩余的人数(游标)。

触发器t4：

create or replace trigger t4
  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 ;

系统触发器

1、通过触发器记录是何用户，何时登录了系统
（1）建表：

create table u_1
( username varchar2(50),
  activity varchar2(20),
  time date
);

（2）触发器st1：

create or replace trigger st1
after logon on database
begin
  insert into u_1 values(user,'logon',sysdate);
end st1;

2、建一触发器，作用为禁止在休息日（周六，周天）改变scott.emp雇员信息。（包括添加，删除，修改）
触发器t1：

create or replace trigger t1
  before insert or delete or update on scott.emp
begin
  if to_char(sysdate,'DAY') in ('星期六','星期日')
    then raise_application_error(-20001,'不能在休息日修改员工信息');
  end if;
end t1;