触发器

来源：互联网发布：php 数组取值编辑：程序博客网时间：2024/06/01 09:51

触发器

　　是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

　　功能：

　　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_id<>80 )

　　begin

　　 :new_value.commission_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 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_id<>80 )

　　限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。

　　其中的new_value是代表更新之后的值。

　　4、触发操作

　　是触发器的主体

　　begin

　　 :new_value.commission_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@hotmail.com’,60,10000,.25);

　　select commission_pct from employees where employee_id=12345;

　　触发器不会通知用户，便改变了用户的输入值。

　　触发器类型：

　　1、语句触发器

　　2、行触发器

　　3、 INSTEAD OF 触发器

　　4、系统条件触发器

　　5、用户事件触发器

是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上

进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论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 don’t have access to modify this table.’);

　　 End if;

　　End;

　　即使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 or 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 条件谓词，作判断：

　　begin

　　 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 employees_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)

　　 Values( user, l_action,sysdate);

　　 End;

　　3、测试

　　insert into employees_copy( employee_id, last_name, email, hire_date, job_id)

　　 values(12345,’Chen’,’Donny@hotmail’,sysdate,12);

　　select *from employees_log

触发器过程
PL/pgSQL 可以用于定义触发器过程。一个触发器过程是用 CREATE FUNCTION 命令创建的，创建的形式是一个不接受

参数并且返回 trigger 类型的函数。请注意该函数即使在 CREATE TRIGGER 声明里声明为准备接受参数，它也必需

声明为无参数 — 触发器的参数是通过 TG_ARGV 传递的，下面有描述。

在一个 PL/pgSQL 函数当做触发器调用的时候，系统会在顶层的声明段里自动创建几个特殊变量。有如下这些：

NEW
数据类型是 RECORD；该变量为INSERT/UPDATE 操作时保存行（ROW）一级的触发器新的数据库行。在语句级别的触发

器里，这个变量是 NULL。

OLD
数据类型是 RECORD；该变量为 INSERT/UPDATE 操作时保存行（ROW）一级的触发器新的数据库行。在语句级别的触

发器里，这个变量是 NULL。

TG_NAME
数据类型是 name；该变量包含实际触发的触发器名。 fired.

TG_WHEN
数据类型是 text；是一个由触发器定义决定的字符串，要么是 BEFORE 要么是 AFTER。

TG_LEVEL
数据类型是 text；是一个由触发器定义决定的字符串，要么是 ROW 要么是 STATEMENT。

TG_OP
数据类型是 text；是一个说明触发触发器的操作的字符串，可以是 INSERT，UPDATE 或者 DELETE。

TG_RELID
数据类型是 oid；是导致触发器调用的表的对象标识（OID）。

TG_RELNAME
数据类型是 name；是激活触发器调用的表的名称。

TG_NARGS
数据类型是 integer；是在CREATE TRIGGER 语句里面赋予触发器过程的参数的个数。

TG_ARGV[]
数据类型是 text 的数组；是 CREATE TRIGGER语句里的参数。下标从 0 开始记数．非法下标（小于 0 或者大于等于

tg_nargs）导致返回一个 NULL 值。

一个触发器函数必须返回 NULL 或者是一个与导致触发器运行的表的记录/行完全一样的结构的数据。

因BEFORE触发的行级别的的触发器可以返回一个 NULL，告诉触发器管理器忽略对该行剩下的操作（也就是说，随后的

触发器将不再执行，并且不会对该行产生INSERT/UPDATE/DELETE动作）。如果返回了一个非 NULL 的行，那么将继续

对该行数值进行处理。请注意，返回一个和原来的NEW不同的行数值将修改那个将插入或更新的行。我们可能用一个

值直接代替NEW里的某个数值并且返回之，或者我们也可以构建一个完全新的记录/行再返回。

BEFORE 或者 AFTER语句级别的触发器，或者一个AFTER 行级别的触发器的返回值将总是被忽略；它们也可以返回

NULL 来忽略返回值。不过，任何这种类型的触发器仍然可以通过抛出一个错误来退出整个触发器操作。

Example 36-2 显示了一个 PL/pgSQL 写的触发器过程的例子。

Example 36-2. 一个PL/pgSQL触发器过程

下面的例子触发器的作用是：任何时候表中插入或更新了行，当前的用户名和时间都记录入行中。并且它保证给出了

雇员名称并且薪水是一个正数。

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp () RETURNS trigger AS $emp_stamp$
    BEGIN
        -- 检查是否给出了 empname 和 salary
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- 我们必须付帐给谁？
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- 记住何时何人的薪水被修改了
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();另外一个向表里记录变化的方法涉及创建一个新表，然后为后来

发生的每次插入、更新或者删除动作保存一行。这个方法可以当作对一个表的审计。 Example 36-3 显示了一个

PL/pgSQL 写的审计触发器过程的例子。

Example 36-3. 一个用于审计的 PL/pgSQL 触发器过程

这个例子触发器保证了在 emp 表上的任何插入，更新或者删除动作都被记录到了 emp_audit 表里（也就是，审计）

。当前时间和用户名会被记录到数据行里，以及还有执行的操作。

CREATE TABLE emp (
empname text NOT NULL,
salary integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- 在 emp_audit 里创建一行，反映对 emp 的操作，
        -- 使用特殊变量 TG_OP 获取操作类型。
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- 忽略结果，因为它是个 AFTER 触发器
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit()
;触发器的一个用途是维持另外一个表的概要。生成的概要可以用于在某些查询中代替原始表 — 通常可以大大缩小运

行时间。这个技巧经常用于数据仓库，这个时候，需要测量的表（叫事实表）可能会非常巨大。 Example 36-4 演示

了一个 PL/pgSQL 触发器过程的例子，它为某个数据仓库的一个事实表维护一个概要表。

Example 36-4. 一个维护概要表的 PL/pgSQL 触发器过程

下面的模式有一部分是基于 Ralph Kimball 的The Data Warehouse Toolkit 里面的 Grocery Store 例子。

--
-- 主表 - 时间维以及销售事实。
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- 摘要表 - 根据时间的销售。
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- 在 UPDATE，INSERT，DELETE 的时候根新概要字段的函数和触发器。
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

-- 计算增/减量。
IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

ELSIF (TG_OP = 'UPDATE') THEN

            -- 禁止改变 time_key 的更新 -
            -- （可能并不是很强制，因为 DELETE + INSERT 是大多数可能
            -- 产生的修改）。
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

END IF;

        -- 用新数值更新概要行。
        UPDATE sales_summary_bytime
            SET amount_sold = amount_sold + delta_amount_sold,
                units_sold = units_sold + delta_units_sold,
                amount_cost = amount_cost + delta_amount_cost
            WHERE time_key = delta_time_key;

        -- There might have been no row with this time_key (e.g new data!).
        IF (NOT FOUND) THEN
            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );
            EXCEPTION
                --
                -- 捕获两个事务维一个新 time_key 增加数据的冲突条件
                --
                WHEN UNIQUE_VIOLATION THEN
                    UPDATE sales_summary_bytime
                        SET amount_sold = amount_sold + delta_amount_sold,
                            units_sold = units_sold + delta_units_sold,
                            amount_cost = amount_cost + delta_amount_cost
                        WHERE time_key = delta_time_key;

            END;
        END IF;
        RETURN NULL;

END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

sql trigger优点
保证某些数据的正确性，把一些逻辑放在服务器上，特别对于历史数据某些需要维护的，可能会比较方便
缺点
一般会带来多一次的i/o,每次对应的事件，数据库都会去检索是否符合条件，如果符合，执行。效率可能会比较慢一些