oracleday21(触发器)

记录登录到oracle 的用户名和登录时间 

禁止用户在星期天对某一张表进行删除操作

当用户在删除一张表的时候 自动把删除的记录备份到另外一张表中

很多关系数据库中都提供了一种技术 可以在用户进行某种操作的时候 自动的进行另外一种操作 我们把这种技术称为触发器技术

触发器是指存放在数据库中 被隐含执行的存储过程 可以支持dml触发器 还支持基于系统事件(启动数据库 关闭数据库 登录) 和ddl操作建立触发器

当用户登录/退出或操作某个数据对象/或者进行ddl(建表 建view) 引起某个存储过程的值 我们把这个隐含被调用的过程 称为触发器

当用户登录的时候 自动的记录该用户的名字 登录时间 ip

当用户在星期天对某张表进行删除操作的时候 我们提示不能这样做

当用户删除某条记录的时候 自动将该记录保存到另外一张表中

dml(insert delete update)触发器

ddl(create table create view ...drop...) 触发器

系统触发器(与系统相关的触发器 比如 用户登录 退出 启动数据库 关闭数据库)

基本语法

create【or replace】 trigger trigger_name

{before|after} 

{insert|delete"update [of column[,column...]]}

on [schema.] table_name--某张表

[for each row] --行级触发器 如果没有就表级

[when condition]--当什么时候

begin

trigger_body;

end;

在添加数据的时候 提示 添加了一条数据

1、创建一张表

create table my_emp (id number, name varchar2(32));

2、创建一个触发器

create or replace trigger  tri1

after insert on

scott.my_emp

begin

dbms_output.put_line(’添加了一条数据‘);

end;

/

set serverout on; 

在某张表 修改多条数据的时候 提示多次修改了数据 修改了几次就提示几次 行级触发器 和语句级触发器

create or replace trigger tri2

after update on 

scott.my_emp 

for each row --表示这是一个行级触发器 

begin

dbms_output.put_line('修改了一条数据');

end;

/

为了禁止工作人员在休息日改变员工信息 开发人员可以建立before 语句触发器 从而实现数据的安全

create or replace trigger tri1

before delete on 

scott.emp

begin

if  to_char(sysdate,'day') in ('星期日','星期六')  then --to_char 可以返回当前星期 加'day' 的话

dbms_output.put_line('对不起 休息日 不能删除员工'); --会提示 但是仍然会删除

RAISE_APPLICATION_ERROR(-20001,'对不起 休息日 不能删除员工');--这次会阻止 会报错

--procedure raise_application_error (error_number_in in number, error_msg_in in varchar2);

RAISE_APPLICATION_ERROR 这个过程 是oracle 提供的可以传入两个参数 第一个是自定义的错误号 -20000~ -20999 之间 其他可能被占用 

第二个参数是提示一个信息

error_number_in[自定义] 从-20000~-20999之间 这样就不会和oracle 的任何错误代码发生冲突 error_msg_in[自定义] 的长度不能超过2k 否则就截取2k

end if;

end;

使用条件谓词

当触发器同时包含多个触发时间(insert update delete) 时 为了在触发器代码中区分具体的触发事件 可以使用三个条件

inserting updating deleting 

语句触发器  在给出提示时 明确提示用户是进行 insert update 还是delete 操作

更加精准告诉用户

create or replace trigger tri3

before 

insert or update or delete on

scott.emp

begin

case 

when inserting then

dbms_output.put_line('请不要添加');

raise_application_error(-20004,'请不要添加');

when updating then

dbms_output.put_line('请不要修改');

raise_application_error(-20005,'请不要修改');

when deleting then

dbms_output.put_line('请不要删除');

raise_application_error(-20006,'请不要删除');

end case;

end;

dml 触发器

:old 修饰符访问操作完成前  列值

:new 修饰符访问操作完成后 列的值

当触发器被触发时 要使用被插入、更新或是删除的记录中的列值 有时要使用操作前、后列的值

特性 insert update delete

old null 有效 有效

new  有效 有效 null

在修改emp 表员工的薪水时 显示雇员工资修改前和修改后的值

如何确保在修改员工工资不能低于原有工资

create or replace  trigger tri4 

before update on

scott.emp

for each row --如果是表级 不能知道表级的sal 要行级

begin

if :new.sal<:old.sal then

dbms_output.put_line('工资不能低于原来工资');

raise_application_error(-20005,'工资不能低于原来工资');--确保在修改员工工资不能低于原有工资

else

dbms_output.put_line('原来的工资是'||:old.sal||'现在的工资'||:new.sal);

end if ;

end;

编写一个触发器保证用户在删除一张表emp 记录的时候 自动把删除的记录备份到另外一张表中

1、建立一张备份表

create table emp_back(id number . name varchar2(2000) );

create or replace trigger tri4

before delete on

scott.emp 

for each row

begin

--执行语句 

insert into emp_back (id,name) values(:old.empno,:old.ename);

end;

编写 触发器 控制员工的新工资不能低于原来的工资 同时也不能高出 原来工资的20%

create or replace  trigger tri5

before update on

scott.emp 

for each row 

begin

  if (:new.sal<:old.sal   or :new.sal>:old.sal*1.2) then

  dbms_output.put_line('工资范围不对');

--阻止执行

raise_application_error(-20010,'工资范围不对');

end if;

end;