1、触发器语句您还未登录！|登录|注册|帮助CSDN首页资讯论坛博客下载搜索更多CTO俱乐部学生大本营培训充电移动开发软件研发云计算程序员ITeyeTUPchinayuan的专栏目录视图摘要视图订阅精创之作雷神的微软平台安全宝典诚邀译者 移动业界领袖会议上海6.20 CSDN博客频道“移动开发之我见”主题征文活动 【分享季1】：网友推荐130个经典资源，分享再赠分！ 数据库触发器 分类： 数据库 2011-03-31 18:00 2889人阅读 评论(2) 收藏 举报 触发器 Oracle 触发器 : 触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。 功能：
2、 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_id80 ) begin :new_
3、mission_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
4、 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_id80 ) 限制不是必须的。此例表示如果列 department_id 不等于 80 的时候，触发器就会执行。其中的 new_
5、value 是代表跟新之后的值。 4 、 触发操作 是触发器的主体 begin :new_mission_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,60,10000,.25); select commission_
6、pct from employees where employee_id=12345; 触发器不会通知用户，便改变了用户的输入值。 触发器类型： 1 、 语句触发器 2 、 行触发器 3 、 INSTEAD OF 触发器 4 、 系统条件触发器 5 、 用户事件触发器 1 、 语句触发器 是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与 INSERT 、 UPDATE 、 DELETE 或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。 比如，无论 update 多少行，也只会调用一次 update 语句触发器。 例子： 需要对在表
7、上进行 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 dont have access to modify this table.); End if; End; / 即使 SYS ， SYSTEM 用户也不能修改 foo 表 试验 对修改表的时间、人物进行日志记录
8、。 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 Inse
9、rt 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 - e
10、lsif 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.ac
11、tion%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 、 测试 inse
12、rt into employees_copy( employee_id, last_name, email, hire_date, job_id) values(12345,Chen,Donnyhotmail,sysdate,12); select *from employees_log update employees_copy set salary=50000 where employee_id = 12345; 2 、 行触发器 是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外： 1 、 定义语句中包含 FOR EACH ROW 子句 2 、 在 BEFORE F
13、OR 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_id80 ) begin :new_mission_pct :=0; end; / Referencing 子句： 执行 DML 语句之前
14、的值的默认名称是 :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_fo
15、o_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 trans
16、parent 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 INSE
17、RT, 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
18、, phone_number,employee_id emp_id From hr.employees; 尝试更新 email 和 name update pany_phone_book set name=Chen1, Donny1 where emp_id=100 create or replace trigger update_name_company_phone_book INSTEAD OF Update on pany_phone_book Begin Update hr.employees Set employee_id=:new.emp_id, First_name=substr
19、(: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 操作 . 另外一种情况是从多个表查询出来的 . 这样我们就不能对视图进行操作
20、了 , 也就是只能查询 .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 )
21、 - 表的约束 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(CO
22、DE); 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 A
23、ND 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
24、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 、 系统事件触发器 系统事件：数据库启
25、动、关闭，服务器错误 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)
26、, 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 vi
27、ew drop_me_view; drop table drop_me; select *from droped_objects 禁用和启用触发器 alter trigger disable; alter trigger enable; 事务处理： 在触发器中，不能使用 commit / rollback, 因为 ddl 语句具有隐式的 commit ，所以也不允许使用 视图： dba_triggers MS SQLServer 触发器 : SQL Sever 2005 包含的 3 个触发器对象 : AFTER ，数据定义语言 (DDL) 和 INSTEAD-OF 1 AFTER 触发器是存储
28、程序，它发生于数据操作语句作用之后，例如删除语句等。 2 DDL 是 SQL Server 2005 的新触发器，允许响应数据库引擎中对象定义水平事件 ( 例如 :DROP TABLE 语句 ) 。 3 INSTEAD-OF 触发器是对象，在数据库引擎中可以取代数据操作语句而执行。例如 : 将 INSTEAD-OF INSERT 触发器附加到表，告诉数据库执行此触发器 SQL Server 2005 中 DDL 触发器的实现 SQL SERVER 2005 中，新增加了许多新的特性，其中的 DDL 触发器是个不错的选择，根据资料初步学习如下，现整理之： 在 sql server 2000 中，
29、只能为针对表发出的 DML 语句（ INSERT 、 UPDATE 和 DELETE ）定义 AFTER 触发器。 SQL Server 2005 可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。可以为单个 DDL 语句（例如， CREATE_TABLE ）或者为一组语句（例如， DDL_DATABASE_LEVEL_EVENTS ）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每个事件的架构都继承了 Server Events 基础架构。 比如，在 SQL SERVER 2005 中，建立一个叫 DDLTrTest 的数据库，并且建立一个叫
