Oracle-触发器

1.触发器的基本格式

CREATE OR REPLACE TRIGGER T1
BEFORE | AFTER | INSTEAD OF
 DELETE OR INSERT OR UPDATE OF 列名
 ON 视图名或表名
 FOR EACH ROW  [ WHEN    (条件) ] 
  DECLARE
          变量声明部分
  BEGIN
          执行部分
  EXCEPTION
          异常处理部分
  END   触发器名;

2.功能要求：增加一新表XS_1，表结构和表XS相同，用来存放从XS表中删除的记录。

(1).创建表xs_1
create table xs_1 as select * from xs;
truncate table xs_1;
(2).创建一个触发器，当XS表中记录被删除时，请备份下删除的记录，方式为：写到新建表XS_1中,以备查看。
create or replace trigger T1
before delete  on xs
for each row
  begin
    insert into xs_1(xh,xm,zym,xb,cssj,zxf)  values(:old.xh,:old.xm, :old.zym, :old.xb, :old.cssj,:old.zxf);
  end T1;
(3).触发触发器
delete from xs where xh = 121112;

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

(1).创建记录表
create table sql_info(info varchar(10),time date);
(2).创建触发器
create or replace trigger T2
after insert or update or delete 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 T2;
(3).触发触发器
update xs set xh=002 where xh=101112;

4.

当插入新员工时显示新员工的员工号、员工名；
当更新员工工资时，显示修改前后员工工资；
当删除员工时，显示被删除的员工号、员工名。

(1).创建触发器
create or replace trigger T3
before insert or update or delete on scott.emp
for each row 
  begin 
    if inserting then
      dbms_output.put_line('插入新员工---'||:new.empno || '-----' || :new.ename);
      elsif updating then
      DBMS_OUTPUT.PUT_LINE('更新员工工资---'||:old.sal||' '||:new.sal);
      else
         DBMS_OUTPUT.PUT_LINE('删除员工---'||:old.empno||' '|| :old.ename);
         end if;
end T3;
(2).触发触发器
update scott.emp set sal=888 where empno=7369;

5.针对Scott.emp表，记录其相应操作的信息，具体如下:

当执行插入操作时，统计操作后员工人数；
当执行更新工资操作时，统计更新后员工平均工资；
当执行删除操作时，统计删除后各个部门剩余的人数(游标)

(1).创建触发器
create or replace trigger T4
after insert or update or delete on scott.emp
--注意此处不能有for each row
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(*) personNum from scott.emp group by deptno )
        loop 
        DBMS_OUTPUT.PUT_LINE('删除记录后各个部门的部门号和人数为' ||v_s.deptno||' '||v_s.personNum);
        end  loop;
    end if;
end T4;
(2).触发触发器
delete from scott.emp where hiredate<=to_date('1980-12-17','yyyy-mm-dd');

6.系统触发器:通过触发器记录是何用户，何时登陆了系统。

(1).创建登录信息表
 create table u_1 (
   username varchar2(50),
   activity  varchar2(20),
   time date)
(2).创建触发器
create or replace trigger T5
after logon on database
begin
  insert into u_1   values(user,'LOGON',sysdate);
end T5;
(3).触发触发器
用sqlplus登录其他用户，然后在查询表：
select * from u_1;

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

日期型转换为字符串型:
Select to_char(sysdate,'yyyy-MM-dd HH24:mi')  from dual;
Select to_char(sysdate, 'DAY')  from dual;
(1).创建触发器
create or replace trigger T6
before insert or update or delete on scott.emp
begin
  if to_char(sysdate, 'DAY') in ('星期六','星期日') then
    raise_application_error(-20001,'不能在休息日修改员工信息');
  end if;
end T6;
(2).触发触发器
update scott.emp set ename='candy'  where empno=7876;

8.启动和禁止触发器

Alter trigger 触发器名字  disable;
Alter trigger  触发器名字  enable;
关闭某表的所有触发器    alter   table   表名字  disable   all  triggers ;    
开启所有触发器   alter   table   表名字   enable  all triggers ;