SQL--数据库触发器

一个触发器基于一个数据表创建，并与一个或多个数据修改操作（插入、更新或删除）相关联。即这些修改操作是触发器执行的触发条件，只要特定的修改操作发生，则触发器被执行。

可用数据库触发器完成以下任务：

（1） 实现一种复杂的安全政策；

（2） 在同一个表或不同的表中用某列改变另一列的值。

（3） 实现复杂的列值有效性检验。

建立触发器的语法：

CREATE [OR REPLACE] TRIGGER 触发器名 { BEFORE|AFTER}

触发事件 ON 表名

[FOR EACH ROW]

[WHEN 条件]

PL/SQL块

说明:

触发事件是对应于DML的三条语句INSERT 、UPDATE、DELETE。

表名：是与触发器相关的表的名字。

FOR EACH ROW：是可选子句，当使用时，对每条相应行将引起触发器触发。

PL/SQL块：是触发器触发时执行的PL/SQL块，称为触发体（Trigger body）。

条件：是可选项，当条件为真时，触发器触发。

数据库触发器有以下两种：

（1） 语句级触发器:在CREATE TRIGGER 语句中不包含FOR EACH ROW 子句；

（2） 行级触发器：有FOR EACH ROW子句。

区别：语句级触发器对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的列值。行级触发器可对受触发器影响的每一行触发。并且能够访问原列值和通过SQL语句处理新列值。

在触发器体内引用列值

(1) 对于INSERT语句，要被插入的数值包含在：NEW.列名,这里的列名是表中的一列.

(2) 对于UPDATE语句,列的原值包含在 OLD.列名中,数据列的新值在: NEW.列名中。

(3) 对于DELETE语句，将要删除的行的列值放在：OLD.列名中。

触发事件

建立触发器时，需要指定什么事件发生时引起触发。

（1） 用INSERT语句在表中插入新的行。

（2） 用UPDATE语句修改一组行

（3） 用DELETE语句删除一组行；

此外，可将这些触发事件进行组合。

例： create or replace trigger abc after insert or update or delete on students

for each row

begin

dbms_output.put_line(‘触发器被触发’);

end;

分别执行以下语句，均可引起触发器被触发

insert into students

values('34','zhang','r');

update students

set sex='g' where stu_id='34';

delete from students

where stu_id='34'

一个表可能的触发器

(1) BEFORE DELETE、BEFORE INSERT、BEFORE UPDATE、AFTER DELETE、

AFTER INSERT 、AFTER UPDATE六种行级触发器。

(2) BEFORE DELETE、BEFORE INSERT、BEFORE UPDATE、AFTER DELETE、

AFTER INSERT 、AFTER UPDATE六种语句级触发器。

作为某信用卡公司的DBA，可通过数据库触发器实现信用政策。公司研究表明：如果一张信用卡在三天内累计计账超过1000美元，这种情况80%以上可能是信用卡诈骗。

处理办法：

可在一个独立的表中记录每一笔帐。如果三天内计帐总数超过￥1000元。则可同时在另一个表中记录这笔帐。

有CLIENT_CHARGE_LOG和CREDIT_CHARGE_LOG表，结构如下：

CARD_NUMBER AMOUNT VENDOR_ID TRANSACTION_DATE

建立一个触发器，在向CLIENT_CHARGE_LOG插入一条数据时，看在近三天内有计帐是否超过￥1000，如果超过则把这条数据同时插入到CREDIT_CHARGE_LOG表中。

Create or replace trigger client_charge_log_ins_before before

Insert on client_charge_log

For each row

Declare

Total_for_past_3_days number

Begin

Select sum(amount) into total_for_past_3_days

From client_charge_log

Where card_number=:new.card_number and transaction_date>sysdate-3;

If total_for_past_3_days>1000 then

Insert into credit_charge_log

(card_number,amount,vendor_id,transaction_date)

values(:new.card_number,:new.amount, :new.vendor_id,:new.transation_date);

end if;

end;

例，有如下两张表

students score

Stu_id name sex Stu_id Score

1 张三 男 1 76

2 李四 女 2 87

3 王明 男 2 96

在修改STUDNETS表中的STU_ID时，要求同时要求修改SCORE表中的STU_ID,

在不能保证所有应用程序都能实现这一点。所有可以建立一个数据库触发器。如下：

create or replace trigger gg before update on students

for each row

begin

update score

set stu_id=:new.stu_id where

stu_id=:OLD.stu_id;

end;

在执行下列操作时，将引起触发.

Update students

Set stu_id=’4’ where stu_id=’2’;

这样，不但能更改SUDENTS表中的数据。而且同时更改了SCORE表中的数据。

使用触发器设置列的值

触发器的另一种功能是：在SQL语句产生作用前，用一个特定的值给列赋值。

例：有表如下(st)

学号 姓名 班级 专业

1 张三 计网2 网络

在向表插入数据时，只需输入姓名、班级、和专业，而学号自动增加。

create table st

(学号 number,

姓名 varchar(8),

班级 varchar(12),

专业 varchar(14));

create or replace trigger tri_on_st before insert on st

for each row

declare

n number;

begin

select max(学号) into n from st;

if n is null then

n:=1;

else

n:=n+1;

end if;

:new.学号:=n;

end;

注意，本例中学号的值一定是自动增加的。

怎样使触发器失效和生效

方法：

(1) 失效： ALTER TRIGGER 触发器名 DISABLE

(2) 生效: ALTER TRIGGER 触发器名 ENABLE

怎样查看触发器的信息：

触发器的信息存放在USER_TRIGGERS和ALL_TRIGGERS数据字典中。

SELECT TRIGGER_NAME,TRIGGERING_EVENT,TRIGGER_TYPE,STATUS

FROM USER_TRIGGERS

例：假设BLOCK_trade_log用来在NASDAQ（全国证券交易交易协会自动报价系统）记录股票交易。表主要包含以下几项内容：股票标志、交易价格、交易数量、交易时间、三天平均值。触发器用来给三天平均值设置数值。

create table block_trade

(股票标志 varchar(8),

交易价格 number,

交易时间 DATE,

三天平均值 NUMBER)

create or replace trigger block_trade before

insert on block_trade

for each row

declare

running_avg number;

begin

select avg(交易价格) into running_avg

from block_trade

where 股票标志=:new.股票标志 and

交易时间>=sysdate-3;

:new.三天平均值:=running_avg;

end;

create or replace trigger block_trade_log_bi before

insert on block_trade_log

for each row

declare

running_avg number;

begin

select avg(交易价格) into running_avg

from block_trade_log

where 股票标志=:new.股票标志 and

交易时间>=sysdate-3;

:new.三天平均值:=running_avg;

end;

练习：

1、修改SCORE表，增加一个字段grade

然后在SCORE表上建立一个触发器。在向表中录入成绩时，如果成绩大于80则将grade设为“A”，如果大于60则将grade设为”B”,否则设为”C”。

CREATE OR REPLACE TRIGGER ON_INSERT_SCORE BEFORE INSERT ON SCORE

FOR EACH ROW

DECLARE

GR VARCHAR(4);

BEGIN

IF :NEW.SCORE>80 THEN

GR:='A';

ELSIF :NEW.SCORE>60 THEN

GR:='B';

ELSE

GR:='C';

END IF;

:NEW.GRADE:=GR;

END;