oracle实验2023-12-8--触发器

第十四周实验

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

            分析:

1、创建表 xs_1

SQL> create table xs_1 as select * from xs;

Table created

SQL> truncate table xs_1;

Table truncated题目：创建一个触发器，当XS表中记录被删除时，请备份下删除的记录，方式：写到新建表XS_1中,以备查看。

create or replace trigger del_xs

  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 del_xs;

代码：

SQL> select *from xs_1;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

SQL> select *from xs;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

061101 王林   计算机 男 1986/2/10    50

101112 李明   计算机 男 1986/1/30    36

001    张琼   计算机                 45 三好学生

121112 王小二 计算机 男 1986/1/30    36

SQL> delete from xs where xh=001;

1 row deleted

SQL> select *from xs_1;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

001    张琼   计算机                 45

SQL> select *from xs;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

061101 王林   计算机 男 1986/2/10    50

101112 李明   计算机 男 1986/1/30    36

121112 王小二 计算机 男 1986/1/30    36

SQL>

综上所述：备份成功！

触发器示例2

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

Create table sql_info(info varchar(10),time date);

思考：是否可以放到一个触发器中，如可以则需要判断

到底是哪种操作（插入还是更新还是删除）

create or replace trigger t2

  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 t2;

SQL> create table sql_info(info varchar(10),time date);

Table created

SQL> select * from sql_info;

INFO       TIME

---------- -----------、

SQL> insert into xs(xh) values(21212);

1 row inserted

SQL> select * from sql_info;

INFO       TIME

---------- -----------

插入       2023/12/8 1

SQL>

SQL> select * from xs;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

061101 王林   计算机 男 1986/2/10    50

101112 李明   计算机 男 1986/1/30    36

121112 王小二 计算机 男 1986/1/30    36

21212                                  

SQL> delete from xs where xh=21212;

1 row deleted

SQL> select * from xs_1;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

001    张琼   计算机                 45

21212                                  

SQL> select * from sql_info;

INFO       TIME

---------- -----------

插入       2023/12/8 1

删除       2023/12/8 1

SQL>

1.

SQL>  create table emp_1 as select * from scott.emp;

Table created

SQL> truncate table emp_1;

Table truncated

SQL>

create or replace trigger del_scott

  before delete on scott.emp

  for each row

begin

  insert into emp_1 (empno,ename,job,mgr,hiredate,sal,comm,deptno)

     values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

end del_emp;

测试;

SQL> delete from scott.emp where empno = 7499;

1 row deleted

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980/12/17     820.00               20

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987/5/23     1120.00               20

 7900 JAMES      CLERK      7698 1981/12/3      970.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

13 rows selected

SQL> select * from emp_1;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

SQL>

2.

create or replace trigger t2

  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 t2;

SQL> create table sql_info(info varchar(10),time date);

Table created

SQL> select * from sql_info;

INFO       TIME

---------- -----------

SQL> insert into xs(xh) values(21212);

1 row inserted

SQL> select * from sql_info;

INFO       TIME

---------- -----------

插入       2023/12/8 1

SQL> select * from xs;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

061101 王林   计算机 男 1986/2/10    50

101112 李明   计算机 男 1986/1/30    36

121112 王小二 计算机 男 1986/1/30    36

21212                                  

SQL> delete from xs where xh=21212;

1 row deleted

SQL> select * from xs_1;

XH     XM     ZYM    XB CSSJ        ZXF BZ

------ ------ ------ -- ----------- --- --------------------

001    张琼   计算机                 45

21212                                   

SQL> select * from sql_info;

INFO       TIME

---------- -----------

插入       2023/12/8 1

删除       2023/12/8 1

SQL> update scott.emp set ename='CHenwang' where empno=7900;

1 row updated

SQL>  select * from sql_info;

INFO       TIME

---------- -----------

插入       2023/12/8 1

删除       2023/12/8 1

SQL>

3.

CREATE OR REPLACE TRIGGER  op_emp

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 op_emp;

SQL> set serveroutput on;

SQL> insert into scott.emp(empno) values(7369);

7369

1 row inserted

SQL> update scott.emp set ename='LAOLI' where empno=7369;

1 row updated

SQL> delete from scott.emp where empno = 7369;

7369 LAOLI

1 row deleted

4.

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 t4;

SQL> insert into scott.emp(empno) values(7369);

7369

添加记录后总人数为13

1 row inserted

SQL> update scott.emp set ename='LAOLI' where empno=7369;

更新记录后平均工资为 2218.75

1 row updated

SQL> delete from scott.emp where empno = 7369;

7369 LAOLI

删除记录后各个部门的部门号和人数为30 5

删除记录后各个部门的部门号和人数为20 4

删除记录后各个部门的部门号和人数为10 3

1 row deleted