PostgreSQL触发器_for each row execute procedure

1 触发器概述

       触发器的功能就是为了解决这类问题而设计的，当你更新或查询某个资料表时会触动触发器，触发器就会照您所设计的流程，同步去插入、更新、删除其他资料，你不再需要重复下达多次的SQL命令就能达成一连串资料的同步处理。

        触发器是某个数据库操作发生时被自动调用的函数。可以在INSERT、UPDATE或DELETE操作之前或之后调用触发器。PostgreSQL支持两种类型的触发器，一种是数据行级触发器，另外一种是语句级触发器。对于数据行级的触发器，触发发触发器的语句每操作一个数据行，它就被执行一次。对于语句级的触发器，它只会被执行一次。

         创建触发器以前，必须定义触发器使用的函数。这个函数不能有任何参数，它的返回值的类型必须是trigger。函数定义好以后，用命令CREATE TRIGGER创建触发器。多个触发器可以使用同一个函数。

         触发器按按执行的时间被分为before触发器和after触发器。语句级的before触发器在语句开始执行前被调用，语句级的after触发器在语句开始执行结束后被调用。数据行级的before触发器在操作每个数据行以前被调用，数据行级的after触发器在操作每个数据行以后被调用。

          语句级的触发器应该返回NULL。

         行级的before触发器的返回值不同，它对触发触发器的操作的影响也不同。如果它返回NULL, 触发这个触发器的INSERT/UPDATE/DELETE命令不会被执行。如果行级的BEFORE触发器返回非空的值，则INSERT/UPDATE/DELETE命令继续执行。对于UPDATE和INSERT操作触发的行级BEFORE触发器，如果它返回的数据行与更新以后的或被插入的数据行不相同，则以触发器返回的数据行作为新的更新以后的数据行和被插入的数据行。

           行级after触发器的返回值总是被忽略，可以返回NULL。

          如果同一表上同对同一个事件定义了多个触发器，这些触发器将按它们的名字的字母顺序被触发。对于行级before触发器来说，前一个触发器返回的数据行作为后一个触发器的输入。如果任何一个行级before触发器返回NULL，后面的触发器将停止执行，触发触发器的INSERT/UPDATE/DELETE命令也不会被执行。

          行级before触发器一般用于检查和修改将被插入和更新的数据。行级after触发器一般用于将表中被更新的数据记录到其它的表中，或者检查与其它的表中的数据是否是一致的。

           before触发器的执行效率比after触发器高，在before触发器和after触发器都能被使用的情况下，应该选择before触发器。

           一个触发器在执行的过程中，如果执行了其它的SQL命令，可能会触发其它的触发器，这被称作触发器级联。对于触发器级联的层次，系统没有任何限制，但触发器级联可能会调用前面已经执行过的触发器，从而引起死循环，系统不会检测这种现象，定义触发器的用户应该保证这种现象不会发生。

           定义触发器的时候，也可以为它指定参数（在CREATE TRIGGER命令中中指定）。系统提供了特殊的接口来访问这些参数。

          触发器在被调用时，系统会自动传递一些数据给它，这些数据包括触发触发器的事件类型（例如INSERT或UPDATE），对于行级触发器，还包括NEW数据行（对于INSERT和 UPDATE触发器）和OLD数据行（对于UPDATE和DELETE触发器）。每种可以用来书写触发器函数的语言都提供了取这些数据的方法。

          语句级别的触发器在执行过程中无法查看该语句插入、删除或更新的任何数据行。《PL/pgSQL教程》第8章中有触发器的实例。

            还有一种特殊的触发器叫约束触发器，这种触发器的执行时间可以被命令SET CONSTRAINTS控制，详细信息参考《SQL命令手册》对CREATE CONSTRAINT TRIGGER命令的解释。 

2 数据可见规则

    触发器在执行过程中，如果执行SQL命令访问触发器的父表中的数据，这些SQL命令遵循下面的数据可见规则，这些规则决定它们能否看见触发触发器的操作修改的表中的数据行：

（1）语句级的before触发器在执行过程中，该语句的所有的对表中的数据的更新对它都不可见。语句级的after触发器在执行过程中，该语句的所有的对表中的数据的更新对它都可见。

（2）行级before触发器在执行过程中，前面所有的已经被同一个命令处理的数据行对它是可见的，但触发该触发器的数据行的更新操作的结果（插入、更新或删除）对它是不可见的。行级after触发器在执行过程中，前面所有的已经被同一个命令处理的数据行对它是可见的。

3 事例

 可以用PL/pgSQL 来写触发器过程。可以用命令CREATE FUNCTION创建一个触发器过程，这个函数没有任何参数，返回值的类型必须是trigger。使用命令CREATE TRIGGER来创建一个触发器，通过TG_ARGV来传递参数给触发器过程，下面会介绍TG_ARGV的用法。

    当一个PL/pgSQL 函数作为一个触发器被调用时，系统自动在最外层的块创建一些特殊的变量。这些变量分别是：

（1）NEW

数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。

（2）OLD

数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。

（3）TG_NAME

数据类型是name，它保存实际被调用的触发器的名字。

（4）TG_WHEN

数据类型是text，根据触发器定义信息的不同，它的值是BEFORE 或AFTER。

（5）TG_LEVEL

数据类型是text，根据触发器定义信息的不同，它的值是ROW或STATEMENT。

（6）TG_OP

数据类型是text，它的值是INSERT、UPDATE或DELETE，表示触发触发器的操作类型。

（7）TG_RELID

数据类型是oid，表示触发器作用的表的oid。

（8）TG_RELNAME

数据类型是name，表示触发器作用的表的名字。它与下面的变量TG_TABLE_NAME的作用是一样的。

（9）TG_TABLE_NAME

数据类型是name，表示触发器作用的表的名字。

（10）TG_TABLE_SCHEMA

数据类型是name，表示触发器作用的表所在的模式。

（11）TG_NARGS

数据类型是integer，表示CREATE TRIGGER命令传给触发器过程的参数的个数。

（12）TG_ARGV[]

数据类型是text类型的数组。表示CREATE TRIGGER命令传给触发器过程的所有参数。下标从0开始。TG_ARGV[0]表示第一个参数，TG_ARGV[1]表示第二个参数，以此类推。 如果下标小于0或大于等于tg_nargs，将会返回一个空值。

    触发器函数必须返回一个NULL或者一个记录/数据行类型的变量，这个变量的结构必须与触发器作用的表的结构一样。

    对于行级的BEFORE触发器，如果返回NULL，后面的触发器将不会被执行，触发这个触发器的INSERT/UPDATE/DELETE命令也不会执行。如果行级的BEFORE触发器返回非空的值，则INSERT/UPDATE/DELETE命令继续执行。对于UPDATE和INSERT操作触发的行级BEFORE触发器，如果它返回的数据行与更新以后的或被插入的数据行不相同，则以触发器返回的数据行作为新的更新好的数据行和被插入的数据行。

    语句级的触发器的返回值和AFTER类型的行级触发器的返回值总是被忽略，没有任何意义。

如果触发器在执行的过程中遇到或者发出了错误，触发触发器的操作将被终止。

下面是一些触发器实例：

（1）使用一个行级BEFORE触发器检查表emp的 被插入或跟新操作完成以后的数据行在列salary上的值是否大于0，列name是否不是空值： 

CREATE TABLE emp (

    empname text,

    salary integer,

    last_date timestamp,

    last_user text

);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$

    BEGIN

        -- Check that empname and salary are given

        IF NEW.empname IS NULL THEN

            RAISE EXCEPTION 'empname cannot be null';

        END IF;

        IF NEW.salary IS NULL THEN

            RAISE EXCEPTION '% cannot have null salary', NEW.empname;

        END IF;

        -- Who works for us when she must pay for it?

        IF NEW.salary < 0 THEN

            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;

        END IF;

        -- Remember who changed the payroll when

        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();

（2）将表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

        --

        -- Create a row in emp_audit to reflect the operation performed on emp,

        -- make use of the special variable TG_OP to work out the operation.

        --

        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;

        ELSIF (TG_OP = 'INSERT') THEN

            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;

            RETURN NEW;

        END IF;

        RETURN NULL; -- result is ignored since this is an AFTER trigger

    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();

（3）这是一个更复杂的例子，表sales_fact存放着表time_dimension的汇总数据，利用表time_dimension上的一个行级BEFORE触发器保持sales_fact和time_dimension中的数据同步。

--

-- Main tables - time dimension and sales fact.

--

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

--

-- Summary table - sales by time.

--

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

--

-- Function and trigger to amend summarized column(s) on 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

        -- Work out the increment/decrement amount(s).

        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

            -- forbid updates that change the time_key -

            -- (probably not too onerous, as DELETE + INSERT is how most

            -- changes will be made).

            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;

        -- Insert or update the summary row with the new values.

        <<insert_update>>

        LOOP

            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;

            EXIT insert_update WHEN found;   

            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

                           );

                EXIT insert_update;

            EXCEPTION

                WHEN UNIQUE_VIOLATION THEN

                    -- do nothing

            END;

        END LOOP insert_update;

        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();

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);

INSERT INTO sales_fact VALUES(1,2,1,20,5,35);

INSERT INTO sales_fact VALUES(2,2,1,40,15,135);

INSERT INTO sales_fact VALUES(2,3,1,10,1,13);

SELECT * FROM sales_summary_bytime;

DELETE FROM sales_fact WHERE product_key = 1;

SELECT * FROM sales_summary_bytime;

UPDATE sales_fact SET units_sold = units_sold * 2;

SELECT * FROM sales_summary_bytime;

（3）

createlang plpgsql DATABASE

CREATE OR REPLACE FUNCTION after_alphas_id() RETURNS trigger AS $BODY$
BEGIN
  IF( TG_OP='DELETE' ) THEN
    UPDATE titles SET alpha_at=null WHERE id=OLD.title_id;
  ELSE
    UPDATE titles SET alpha_at=NOW() WHERE id=NEW.title_id;
  END IF;
  RETURN NULL;
END;

$BODY$
  LANGUAGE 'plpgsql';

CREATE TRIGGER after_alphas_id
  AFTER INSERT OR DELETE
  ON alphas
  FOR EACH ROW
  EXECUTE PROCEDURE after_alphas_id();