数据库触发器----ORACLE

最新推荐文章于 2024-07-19 07:01:13 发布

liupeng900605

最新推荐文章于 2024-07-19 07:01:13 发布

阅读量802

点赞数

分类专栏： DataBase 文章标签：数据库 insert triggers table action each

DataBase 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

功能：

1 、允许 / 限制对表的修改

2 、自动生成派生列，比如自增字段

3 、强制数据一致性

4 、提供审计和日志记录

5 、防止无效的事务处理

6 、启用复杂的业务逻辑

开始 :

create trigger biufer_employees_department_id

before insert or update

of department_id

on employees

referencing old as old_value

new as new_value

for each row

when (new_value.department_id<>80 )

begin

:new_value.commission_pct :=0;

end;

触发器的组成部分：

1 、触发器名称

2 、触发语句

3 、触发器限制

4 、触发操作

1 、触发器名称

create trigger biufer_employees_department_id

命名习惯：

biufer （ before insert update for each row ）

employees 表名

department_id 列名

2 、触发语句

比如：

表或视图上的 DML 语句； DDL 语句，数据库关闭或启动 ,startup shutdown 等等

before insert or update

of department_id

on employees

referencing old as old_value

new as new_value

for each row

说明：

（ 1 ）、无论是否规定了 department_id ，对 employees 表进行 insert 的时候

（ 2 ）、对 employees 表的 department_id 列进行 update 的时候

3 、触发器限制

when (new_value.department_id<>80 )

限制不是必须的。此例表示如果列 department_id 不等于 80 的时候，触发器就会执行。其中的 new_value 是代表跟新之后的值。

4 、触发操作

是触发器的主体

begin

:new_value.commission_pct :=0;

end;

主体很简单，就是将更新后的 commission_pct 列置为 0

触发：

insert into employees
(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);

select commission_pct from employees where employee_id=12345;

触发器不会通知用户，便改变了用户的输入值。

触发器类型：

1 、语句触发器

2 、行触发器

3 、 INSTEAD OF 触发器

4 、系统条件触发器

5 、用户事件触发器

1 、语句触发器

是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与 INSERT 、 UPDATE 、DELETE 或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论 update 多少行，也只会调用一次 update 语句触发器。

例子：

需要对在表上进行 DML 操作的用户进行安全检查，看是否具有合适的特权。

Create table foo(a number);

Create trigger biud_foo

Before insert or update or delete

On foo

Begin

If user not in (‘DONNY’) then

Raise_application_error(-20001, ‘You don’t have access to modify this table.’);

End if;

End;

即使 SYS ， SYSTEM 用户也不能修改 foo 表

[ 试验 ]

对修改表的时间、人物进行日志记录。

1 、建立试验表

create table employees_copy as select *from hr.employees

2 、建立日志表

create table employees_log( who varchar2(30), when date);

3 、在 employees_copy 表上建立语句触发器，在触发器中填充 employees_log 表。

Create or replace trigger biud_employee_copy

Before insert or update or delete

On employees_copy

Begin

Insert into employees_log( Who,when) Values( user, sysdate);

End;

4 、测试

update employees_copy set salary= salary*1.1;

select *from employess_log;

5 、确定是哪个语句起作用？

即是 INSERT/UPDATE/DELETE 中的哪一个触发了触发器？

可以在触发器中使用 INSERTING / UPDATING / DELETING 条件谓词，作判断：

begin

if inserting then

-----

elsif updating then

-----

elsif deleting then

------

end if;

end;

if updating(‘COL1’) or updating(‘COL2’) then

------

end if;

[ 试验 ]

1 、修改日志表

alter table employees_log add (action varchar2(20));

2 、修改触发器，以便记录语句类型。

Create or replace trigger biud_employee_copy

Before insert or update or delete

On employees_copy

Declare

L_action employees_log.action%type;

Begin

if inserting then

l_action:=’Insert’;

elsif updating then

l_action:=’Update’;

elsif deleting then

l_action:=’Delete’;

else

raise_application_error(-20001,’You should never ever get this error.’);

Insert into employees_log( Who,action,when) Values( user, l_action,sysdate);

End;

3 、测试

insert into employees_copy( employee_id, last_name, email, hire_date, job_id)

values(12345,’Chen’,’Donny@hotmail’,sysdate,12);

select *from employees_log

update employees_copy set salary=50000 where employee_id = 12345;

2 、行触发器

是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：

1 、定义语句中包含 FOR EACH ROW 子句

2 、在 BEFORE …… FOR EACH ROW 触发器中，用户可以引用受到影响的行值。

比如：

定义：

create trigger biufer_employees_department_id

before insert or update

of department_id

on employees_copy

referencing old as old_value

new as new_value

for each row

when (new_value.department_id<>80 )

begin

:new_value.commission_pct :=0;

end;

Referencing 子句：

执行 DML 语句之前的值的默认名称是 :old , 之后的值是 :new

insert 操作只有 :new

delete 操作只有 :old

update 操作两者都有

referencing 子句只是将 new 和 old 重命名为 new_value 和 old_value ，目的是避免混淆。比如操作一个名为new 的表时。作用不很大。

[ 试验 ] ：为主健生成自增序列号

drop table foo;

create table foo(id number, data varchar2(20));

create sequence foo_seq;

create or replace trigger bifer_foo_id_pk

before insert on foo

for each row

begin

select foo_seq.nextval into :new.id from dual;

end;

insert into foo(data) values(‘donny’);

insert into foo values(5,’Chen’);

select * from foo;

3 、 INSTEAD OF 触发器更新视图

instead of 触发器，可以实现 : 不执行导致 trigger 触发的语句，而只执行触发器 .

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

Create or replace view company_phone_book as

Select first_name||’, ’||last_name name, email, phone_number,employee_id emp_id

From hr.employees;

尝试更新 email 和 name

update hr.company_phone_book set name=’Chen1, Donny1’ where emp_id=100

create or replace trigger update_name_company_phone_book

INSTEAD OF

Update on hr.company_phone_book

Begin

Update hr.employees

Set employee_id=:new.emp_id,

First_name=substr(:new.name, instr(:new.name,’,’)+2),

last_name= substr(:new.name,1,instr(:new.name,’,’)-1),

phone_number=:new.phone_number,

email=:new.email

where employee_id=:old.emp_id;

end;

instead of trigger 是基于视图建立的 , 不能建在表上 , 为什么要建在视图上 , 一般的视图如果其数据来源一个表并且包含该表的主键 , 就可以对视图进行 DML 操作 . 另外一种情况是从多个表查询出来的 . 这样我们就不能对视图进行操作了 , 也就是只能查询 .instead of trigger 可以解决建在多表上视图的更新操作 .

下面我们就来实例操作 :

a. 先建表 , 简单点就三个分别是学生表 , 课程表 , 学生选课表

CREATE TABLE STUDENT

(

CODE VARCHAR2(5),

LNAME VARCHAR2(200)

)

CREATE TABLE COURSE

(

CODE VARCHAR2(5),

CNAME VARCHAR2(30)

)

CREATE TABLE ST_CR

(

STUDENT VARCHAR2(5),

COURSE VARCHAR2(5),

GRADE NUMBER

)

-- 表的约束

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT$PK PRIMARY KEY(CODE);

ALTER TABLE COURSE ADD CONSTRAINT COURSE$PK PRIMARY KEY(CODE);

ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$PK PRIMARY KEY(STUDENT, COURSE);

ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$FK$STUDENT FOREIGN KEY(STUDENT) REFERENCES STUDENT(CODE);

ALTER TABLE ST_CR ADD CONSTRAINT ST_CR$FK$COURSE FOREIGN KEY(COURSE) REFERENCES COURSE(CODE);

b. 基于这三个表的视图

CREATE OR REPLACE VIEW STUDENT_STATUS AS

SELECT S.CODE S_CODE, S.LNAME STUDENT, C.CODE C_CODE, C.CNAME COURSE, SC.GRADE GRADE

FROM STUDENT S, COURSE C, ST_CR SC

WHERE S.CODE = SC.STUDENT

AND C.CODE = SC.COURSE

c. 基于视图的触发器

CREATE OR REPLACE TRIGGER TRI_STCR INSTEAD OF INSERT ON STUDENT_STATUS

FOR EACH ROW

DECLARE

W_ACTION VARCHAR2(1);

BEGIN

IF INSERTING THEN

W_ACTION := 'I';

ELSE

RAISE PROGRAM_ERROR;

END IF;

INSERT INTO STUDENT(CODE, LNAME) VALUES(:NEW.S_CODE,:NEW.STUDENT);

INSERT INTO COURSE(CODE, CNAME) VALUES(:NEW.C_CODE, :NEW.COURSE);

INSERT INTO ST_CR(STUDENT, COURSE, GRADE)

VALUES(:NEW.S_CODE, :NEW.C_CODE, :NEW.GRADE);

END;

d. 对视图执行数据插入

INSERT INTO STUDENT_STATUS(S_CODE, STUDENT, C_CODE, COURSE, GRADE)

VALUES('001','Mike','EN','English',86);

可以看到每个表各有一条数据已经插入 .

4 、系统事件触发器

系统事件：数据库启动、关闭，服务器错误

create trigger ad_startup

after startup

on database

begin

-- do some stuff

end;

5 、用户事件触发器

用户事件：用户登陆、注销， CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE / RENAME / TRUNCATE / LOGOFF

例子：记录删除对象

1. 日志表

create table droped_objects(

object_name varchar2(30),

object_type varchar2(30),

dropped_on date);

2 ．触发器

create or replace trigger log_drop_trigger

before drop on donny.schema

begin

insert into droped_objects values(

ora_dict_obj_name, -- 与触发器相关的函数

ora_dict_obj_type,

sysdate);

end;

3. 测试

create table drop_me(a number);

create view drop_me_view as select *from drop_me;

drop view drop_me_view;

drop table drop_me;

select *from droped_objects

禁用和启用触发器

alter trigger <trigger_name> disable;

alter trigger <trigger_name> enable;

事务处理：

在触发器中，不能使用 commit / rollback, 因为 ddl 语句具有隐式的 commit ，所以也不允许使用

视图：

dba_triggers

liupeng900605

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库触发器----ORACLE

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。功能：1 、允许 / 限制对表的修改2 、自动生成派生列，比如自增字段3 、强制数据一致性4 、提供审计和日志记录5 、防止无效的事务处理6 、启用复杂的业务逻辑开始 :create trigger biufer_employees_department_id
复制链接

扫一扫