【Oracle】第八次实验报告

【Oracle】第八次实验报告

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

create or replace trigger del_xs
after delete on xs
for each row
  begin
    insert into xs_1(zh,zm,zym,xb,cssj,zxf)
    values
    (:old.xh,:old.xm,:old.zym,:old.xb,:old.cssj,:old.zxf);
    end del_xs;

触发触发器
SQL> select * from xs
2 ;

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=‘061101’;

1 row deleted

SQL> select * from xs_del;

XH     XM     ZYM    XB CSSJ        ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林   计算机 男 1986/2/10    50 
 

监控用户对xs的操作，当xs表执行插入、更新、删除操作后在sql_info表中给出相应的提示和执行时间
创建表：

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

Table created

创建触发器;

create or replace trigeer 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> insert into xs(xh,xm,zym,xb) values('0012','徐敏','计算机','女');

1 row inserted

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 
0012   徐敏   计算机 女                 

SQL> select * from sql_info;

INGO       TIME
---------- -----------
插入       2021/12/17

SQL> update xs set xb='女' where xh='0012';

1 row updated

SQL> select * from sql_info;

INGO       TIME
---------- -----------
插入       2021/12/17
更新       2021/12/17

SQL> delete from xs where xh='0012';

1 row deleted

SQL> select * from sql_info;

INGO       TIME
---------- -----------
插入       2021/12/17
更新       2021/12/17
删除       2021/12/17

实例3：为emp表创建一个触发器，当插入新员工是显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。

触发器的建立

create or replace trigger t4
  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 t4;

触发器的触发

SQL> select * from scott.emp;

 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 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     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 

14 rows selected

SQL> set serveroutput on;
SQL> delete from scott.emp where empno='7369';

7369 SMITH
1 row deleted

SQL> insert into scott.emp(empno,ename,job) values('7369','LYN','MANAGER');

7369 LYN
1 row inserted

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

1 row updated

SQL> update scott.emp set sal='8000' where empno='7369';

 8000
 
1 row updated

SQL> update scott.emp set sal='10000' where empno='7369';

8000 10000
1 row updated