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

我不是女神ヾ 2024-02-05 13:20 46阅读 0赞

## 第十四周实验 ##

【例】功能要求：增加一新表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中,以备查看。

![e88dbfee4e784a3997701b712919e9f1.png][]

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

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

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

![9446c888240e401ea104409ceef63c04.png][]

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>

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

Table created

SQL> truncate table emp\_1;

Table truncated

SQL>

![cf547f2490a04166bd370790ce56687e.png][]

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>

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>

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

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

[e88dbfee4e784a3997701b712919e9f1.png]: https://img-blog.csdnimg.cn/direct/e88dbfee4e784a3997701b712919e9f1.png
[9446c888240e401ea104409ceef63c04.png]: https://img-blog.csdnimg.cn/direct/9446c888240e401ea104409ceef63c04.png
[cf547f2490a04166bd370790ce56687e.png]: https://img-blog.csdnimg.cn/direct/cf547f2490a04166bd370790ce56687e.png