Oracle中的触发器

Oracle中的触发器

触发器

触发器(tigger)是在事件发生时隐式地自动运行的 PL/SQL 程序块，不能接收参数，不能被调用，就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。

触发器类型

根据触发器所创建的语句及所影响的对象的不同，将触发器分为 3 类，DML 触发器、系统事件触发器、替代触发器（instead of 触发器）。

（1）DML 触发器

对数据表进行 DML 语句操作（如 insert、update、delete）时所触发的触发器，可以分为：

语句级触发器或行级触发器：行级触发器会对数据库表中的受影响的每一行触发一次触发器代码，语句级触发器则只触发一次，与语句所影响到的行数无关
before 触发器或 after 触发器：before 触发器在触发事件发生之前执行触发器代码，
after 触发器则在触发事件发生之后执行。
语法：

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql 语句
end

其中：
触发器名： 触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间： 指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。

触发事件： 指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。

**表名：**数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

触发器能实现如下功能：

功能：
1、允许/限制对表的修改

2、自动生成派生列，比如自增字段

3、强制数据一致性

4、提供审计和日志记录

5、防止无效的事务处理

6、启用复杂的业务逻辑

下面的触发器在更新表 tb_emp 之前触发，目的是不允许在周末修改表

create or replace trigger auth_secure before insert or update or DELETE on tb_emp
begin
	IF(to_char(sysdate,'DY')='星期日') THEN
		RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表 tb_emp');
	END IF;
END;

--- 插入一条数据以后被触发
create or replace trigger testTrigger after 
	insert on tb_emp FOR EACH ROW -- 对表的每一行触发器执行一次
		declare 
			-- local variables here 
begin 
	dbms_output.put_line('一个员工被插入'); 
end testTrigger;

---当用户对 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
	--DELETE 触发
		V_TYPE := 'DELETE';
		DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
	END IF;
		INSERT INTO TEST_LOG VALUES (USER, --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;


--- 创建触发器，比较 emp 表中更新的工资
set serveroutput on;
CREATE OR REPLACE TRIGGER SAL_EMP
	BEFORE UPDATE ON EMP
	FOR EACH ROW
BEGIN
	IF :OLD.SAL > :NEW.SAL THEN --- :old.字段名称 :原来的值
		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); -- :new.字段名称: 待更新的值
END;
--执行 UPDATE 查看效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';

更多相关知识请戳我的主页哦！