postgresql 触发器

╰+哭是因爲堅強的太久メ 2022-08-09 01:54 123阅读 0赞

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 *e**m**p**s**t**a**m**p*

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;

*e**m**p**s**t**a**m**p* 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 *e**m**p**a**u**d**i**t*

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;

*e**m**p**a**u**d**i**t* 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 *m**a**i**n**t**s**a**l**e**s**s**u**m**m**a**r**y**b**y**t**i**m**e*

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;

*m**a**i**n**t**s**a**l**e**s**s**u**m**m**a**r**y**b**y**t**i**m**e* 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）

目标：  
当表alphas插入新行时，更新titles的alpha\_at为NOW()  
当表alphas删除行时，更新titles的alpha\_at为NULL

1、安装plpgsql语言到数据库  
createlang plpgsql DATABASE  
2、建立一个返回为trigger的过程  
CREATE OR REPLACE FUNCTION after\_alphas\_id() RETURNS trigger AS *B**O**D**Y*  
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;

*B**O**D**Y*  
LANGUAGE ‘plpgsql’;  
3、创建触发器  
CREATE TRIGGER after\_alphas\_id  
AFTER INSERT OR DELETE  
ON alphas  
FOR EACH ROW  
EXECUTE PROCEDURE after\_alphas\_id();