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 ) 限制不是必
3、须的。此例表示如果列 department_id 不等于 80 的时候，触发器就会执行。其中的 new_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,
4、sysdate, 12, ,60,10000,.25); select commission_pct from employees where employee_id=12345; 触发器不会通知用户，便改变了用户的输入值。 触发器类型： 1 、 语句触发器 2 、 行触发器 3 、 INSTEAD OF 触发器 4 、 系统条件触发器 5 、 用户事件触发器 1 、 语句触发器 是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与 INSERT 、 UPDATE 、 DELETE 或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。 比
5、如，无论 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 dont have access to modify this table.); End if; End; / 即使
6、 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 o
7、r 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 条件谓词 ，作判断： be
8、gin 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 e
9、mployees_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) Valu
10、es( user, l_action,sysdate); End; / 3 、 测试 insert 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 、 行触发器 是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两
11、个例外： 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_id80 ) begin :new_miss
12、ion_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 seque
13、nce 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 触发的语
14、句，而只执行触发器 . 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
15、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_boo
16、k as Select first_name|, |last_name name, email, 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
17、 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 是基于视图建立的 , 不能建在表上 , 为什么要建在视图上 , 一般的视图如果其数据来源一个表并且包含该表的主键 , 就可以对
18、视图进行 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
19、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$STU
20、DENT 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,
21、 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)
22、 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
23、,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( obj
24、ect_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 vie
25、w drop_me_view as select *from drop_me; drop view 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 个触发器对象 :
26、AFTER ，数据定义语言 (DDL) 和 INSTEAD-OF 1 AFTER 触发器是存储程序，它发生于数据操作语句作用之后，例如删除语句等。 2 DDL 是 SQL Server 2005 的新触发器，允许响应数据库引擎中对象定义水平事件 ( 例如 :DROP TABLE 语句 ) 。 3 INSTEAD-OF 触发器是对象，在数据库引擎中可以取代数据操作语句而执行。例如 : 将 INSTEAD-OF INSERT 触发器附加到表，告诉数据库执行此触发器 SQL Server 2005 中 DDL 触发器的实现 SQL SERVER 2005 中，新增加了许多新的特性，其中的 DDL 触
27、发器是个不错的选择，根据资料初步学习如下，现整理之： 在 sql server 2000 中，只能为针对表发出的 DML 语句（ INSERT 、 UPDATE 和 DELETE ）定义 AFTER 触发器。 SQL Server 2005 可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。可以为单个 DDL 语句（例如， CREATE_TABLE ）或者为一组语句（例如， DDL_DATABASE_LEVEL_EVENTS ）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每
28、个事件的架构都继承了 Server Events 基础架构。 比如，在 SQL SERVER 2005 中，建立一个叫 DDLTrTest 的数据库，并且建立一个叫 mytable 的表和 Usp_Querymytable 的存储过程，如下所示 DROP DATABASE DDLTRTEST GO CREATE DATABASE DDLTRTEST GO USE DDLTRTEST GO IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(NDBO.MYTABLE) AND TYPE IN (NU) DROP TAB
29、LE DBO.MYTABLE GO CREATE TABLE MYTABLE(ID INT, NAME VARCHAR(100) GO INSERT INTO MYTABLE SELECT 1,A INSERT INTO MYTABLE SELECT 2,B INSERT INTO MYTABLE SELECT 3,C INSERT INTO MYTABLE SELECT 4,D INSERT INTO MYTABLE SELECT 5,E INSERT INTO MYTABLE SELECT 6,F GO USE DDLTrTest GO IF EXISTS (SELECT * FROM s
30、ys.objects WHERE object_id = OBJECT_ID(Ndbo.usp_querymytable) AND type in (NP, NPC) DROP PROCEDURE dbo.usp_querymytable GO CREATE PROC USP_QUERYMYTABLE AS SELECT * FROM MYTABLE GO 接下来定义一个 DDL 触发器如下 : CREATE TRIGGER STOP_DDL_on_Table_and_PROC ON DATABASE FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE, CRE
31、ATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE AS SELECT EVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1, nvarchar(max) PRINT You are not allowed to CREATE,ALTER and DROP any Tables and Procedures ROLLBACK; 接下来，我们尝试如下的操作： ALTER TABLE MYTABLE ADD X INT 结果如下，出现错误提示 ALTER TABLE MYTABLE ADD X
32、 INT (1 row(s) affected) You are not allowed to CREATE,ALTER and DROP any Tables and Procedures Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. 再执行 DROP 的操作，同样触发警告 DROP TABLE MYTABLE (1 row(s) affected) You are not allowed to CREATE,ALTER and DRO
33、P any Tables and Procedures Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. 因为我们的触发器规定了不能使用 CREATE_TABLE,DROP_TABLE, ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE 等操作。 如果我们要关掉这个触发器，可以这样做： DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON DATABASE 当然，我们要对整个服务器采取策略的话，也是很简单的，和上面的方法大致相同只不过将 on database 的参数改为 on server, 比如
