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