1.创建一个触发器,当xs表中记录被删除时,请备份下删除的记录,方式:写到新建表xs_del中,以备查看。
SQL> set serveroutput on
SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp
SQL> create table xs_del as select * from xs;
Table created
SQL> truncate table xs_del;
Table truncated
SQL> select * from xs_del;
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
触发器t1:
create or replace trigger t1
before delete on xs
for each row
declare
-- local variables here
begin
insert into xs_del(xh,xm,zym,xb,cssj,zxf)
values (:old.xh,:old.xm, :old.zym, :old.xb, :old.cssj,:old.zxf);
end t1;
SQL> delete from xs where xh='121112';
1 row deleted
SQL> select *from xs_del;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
121112 王小二 计算机 男 1986/1/30 36
SQL> select * from xs;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
001 张琼 计算机 45 三好学生
2.监控用户对xs表的操作,要求:当xs表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间。
SQL> create table sql_info(info varchar2(10),time date);
Table created
触发器t2:
create or replace trigger t2
after delete or insert or update on xs
for each row
declare
-- local variables here
v_info sql_info.info%type;
begin
if inserting then
v_info:='插入';
elsif deleting then
v_info:='删除';
else
v_info:='更新';
end if;
insert into sql_info values(v_info,sysdate);
end t2;
SQL> select * from sql_info;
INFO TIME
---------- -----------
SQL> update xs set zxf=60 where xm='张琼';
1 row updated
SQL> select * from sql_info;
INFO TIME
---------- -----------
更新 2021/12/17