Oracle触发器详解:概念、应用与语法

2 下载量 42 浏览量 更新于2024-08-28 收藏 131KB PDF 举报
Oracle触发器是数据库管理系统中的一种特殊对象,它们是与表关联的PL/SQL代码块,设计用于在特定的数据操作(如INSERT、UPDATE或DELETE)发生时自动执行。触发器的核心概念是它们提供了对数据修改的实时监控和控制,可以在操作发生前后或针对每一行数据进行响应。 触发器的应用场景广泛,包括但不限于以下几个方面: 1. **复杂安全性检查**:在数据插入、更新或删除前,可以执行复杂的权限验证和访问控制,确保只有授权用户才能执行特定操作。 2. **数据确认和完整性**:确保数据满足预设的业务规则,比如在更新记录前检查是否存在冲突或完整性约束。 3. **数据库审计**:记录所有敏感操作,便于跟踪和合规性审计。 4. **数据备份和恢复**:自动备份数据,或者在事务提交时进行日志处理,便于数据恢复。 5. **业务逻辑扩展**:实现复杂的业务流程,如在订单创建后发送邮件通知、库存管理等。 触发器的语法结构包括以下几个关键部分: - **CREATE OR REPLACE TRIGGER**:用于创建或替换现有的触发器。 - **trigger_name**:用户自定义的触发器名称,用于唯一标识触发器。 - **BEFORE/AFTER INSERT/DELETE/UPDATE**:定义触发器在数据操作前或后的执行时机。 - **ON [schema.]table_name**:指定了触发器关联的表或视图。 - **REFERENCING**:用于引用旧值(OLD)或新值(NEW),以及可能的父实体(PARENT)。 - **FOREACHROW**:选择行触发器,每行数据操作都会独立触发,适用于多行操作。 - **WHEN condition**:可选的条件语句,仅当满足条件时触发。 - **PL/SQL_BLOCK**:触发器的实际执行代码,可以包含变量声明和PL/SQL语句。 举例来说,如文章中提到的`insertStaffHint`触发器,它在员工表`xgj_test`的INSERT操作后执行,当新记录插入时,会调用`dbms_output.put_line()`函数打印消息“新增员工成功”。这个触发器属于行触发器,因为它会在每行插入操作后单独触发。 总结来说,Oracle触发器是数据库管理和数据一致性控制的重要工具,它们能够自动化执行额外的操作,确保数据的一致性和完整性,同时也可以增强数据库的安全性和审计功能。掌握触发器的语法和使用场景,是任何Oracle数据库管理员或开发人员必须具备的基本技能。
2010-11-23 上传
oracle触发器实例讲解2008-11-27 09:17--[6]// Oracle Trigger ---------------------------------------------------------------------------------------------// --实例1------------------------ --创建触发器,当用户对test表执行DML语句时,将相关信息记录到日志表 --创建测试表 CREATE TABLE test ( t_id NUMBER(4), t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR ); --创建记录测试表 CREATE TABLE test_log ( l_user VARCHAR2(15), l_type VARCHAR2(15), l_date VARCHAR2(30) ); --创建触发器 CREATE OR REPLACE TRIGGER test_trigger AFTER DELETE OR INSERT OR UPDATE ON test DECLARE v_type test_log.l_type%TYPE; BEGIN IF INSERTING THEN --INSERT触发 v_type := 'INSERT'; DBMS_OUTPUT.PUT_LINE('记录已经成功插入,并已记录到日志'); ELSIF UPDATING THEN --UPDATE触发 v_type := 'UPDATE'; DBMS_OUTPUT.PUT_LINE('记录已经成功更新,并已记录到日志'); ELSIF DELETING THEN v_type := 'DELETE'; DBMS_OUTPUT.PUT_LINE('记录已经成功删除,并已记录到日志'); END IF; INSERT INTO test_log VALUES(user,v_type, TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')); END; / --下面我们来分别执行DML语句 INSERT INTO test VALUES(101,'zhao',22,'M'); UPDATE test SET t_age = 30 WHERE t_id = 101; DELETE test WHERE t_id = 101; --然后查看效果 SELECT * FROM test; SELECT * FROM test_log; --实例2------------------------ --创建触发器,它将映射emp表中每个部门的总人数和总工资 --创建映射表 CREATE TABLE dept_sal AS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; DESC dept_sal; --创建触发器 CREATE OR REPLACE TRIGGER emp_info AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE CURSOR cur_emp IS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; BEGIN DELETE dept_sal; --触发时首先删除映射表信息 FOR v_emp IN cur_emp LOOP --DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal); --插入数据 INSERT INTO dept_sal VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal); END LOOP; END; / --对emp表进行DML操作 INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000); SELECT * FROM dept_sal; DELETE EMP WHERE empno=123; SELECT * FROM dept_sal; --实例3------------------------ --创建触发器,它记录表的删除数据 --创建表 CREATE TABLE employee ( id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex CHAR NOT NULL ); DESC employee; --插入数据 INSERT INTO employee VALUES('e101','zhao',23,'M'); INSERT INTO employee VALUES('e102','jian',21,'F'); --创建记录表 CREATE TABLE old_employee AS SELECT * FROM employee; DESC old_employee; --创建触发器 CREATE OR REPLACE TRIGGER tig_old_emp AFTER DELETE ON employee -- FOR EACH ROW --语句级触发,即每一行触发一次 BEGIN INSERT INTO old_employee VALUES(:old.id,:old.name,:old.age,:old.sex); --:old代表旧值 END; / --下面进行测试 DELETE employee; SELECT * FROM old_employee; --实例4------------------------ --创建触发器,利用视图插入数据 --创建表 CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2)); CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30)); --插入数据 INSERT INTO tab1 VALUES(101,'zhao',22); INSERT INTO tab1 VALUES(102,'yang',20); INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou'); INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou'); --创建视图连接两张表 CREATE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid; --创建触发器 CREATE OR REPLACE TRIGGER tab_trigger INSTEAD OF INSERT ON tab_view BEGIN INSERT INTO tab1(tid,tname) VALUES(:new.tid,:new.tname); INSERT INTO tab2(ttel,tadr) VALUES(:new.ttel,:new.tadr); END; / --现在就可以利用视图插入数据 INSERT INTO tab_view VALUES(105,'zhaoyang','13886681288','beijing'); --查看效果 SELECT * FROM tab_view; --实例5------------------------ --创建触发器,比较emp表中更新的工资 CREATE OR REPLACE TRIGGER sal_emp BEFORE UPDATE ON emp FOR EACH ROW BEGIN IF :OLD.sal > :NEW.sal THEN DBMS_OUTPUT.PUT_LINE('工资减少'); ELSIF :OLD.sal < :NEW.sal THEN DBMS_OUTPUT.PUT_LINE('工资增加'); ELSE DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); END IF; DBMS_OUTPUT.PUT_LINE('更新前工资 :' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('更新后工资 :' || :NEW.sal); END; / --执行UPDATE查看效果 UPDATE emp SET sal = 3000 WHERE empno = '7788'; --实例6------------------------ --创建触发器,将操作CREATE、DROP存储在log_info表 --创建表 CREATE TABLE log_info ( manager_user VARCHAR2(15), manager_date VARCHAR2(15), manager_type VARCHAR2(15), obj_name VARCHAR2(15), obj_type VARCHAR2(15) ); --创建触发器 CREATE OR REPLACE TRIGGER trig_log_info AFTER CREATE OR DROP ON SCHEMA BEGIN INSERT INTO log_info VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE); END; / --测试语句 CREATE TABLE a(id NUMBER); CREATE TYPE aa AS OBJECT(id NUMBER); / DROP TABLE a; DROP TYPE aa; --查看效果 SELECT * FROM log_info; --相关数据字典-----------------------------------------------------// SELECT * FROM USER_TRIGGERS; SELECT * FROM ALL_TRIGGERS; SELECT * FROM DBA_TRIGGERS; --必须以DBA身份登陆才能使用此数据字典 --启用和禁用 ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE;