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

触发器和存储过程的区别

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

实验题

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

create or replace trigger tr_lbefore delete on xsfor each row
declarebegininsert 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_2after delete or insert or update on xsfor each row
declarev_info sql_info.info%type;
beginif inserting thenv_info:='插入';elsif updating thenv_info:='更新';elsev_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_3after delete or insert or update on xsfor each row
declarev_info sql_info.info%type;
beginif inserting thenv_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 thenv_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);elsev_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 t4after insert or update or delete on scott.emp  
declarev_1 number;v_2 scott.emp.sal%type;
beginif inserting thenselect count(*) into v_1 from scott.emp;dbms_output.put_line('添加记录后的总人数为：'||v_1);elsif updating thenselect avg(sal) into v_2 from scott.emp;dbms_output.put_line('更新记录后的平均工资为：'||v_2);elsefor v_s in (select deptno,count(*) num from scott.emp group by deptno)loopdbms_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
begininsert into u_1 values(user,'logon',sysdate);
end st1;

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

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

在这里插入图片描述