SQL--数据库触发器

skywx

本人也刚刚在学习ORACLE，特把自已学习的一些资料放上来，供兄弟们参考一下
现在正在学习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;