postgresql触发器

前端之家收集整理的这篇文章主要介绍了postgresql触发器前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

转载:http://www.PHP100.com/manual/Postgresql8/plpgsql-trigger.html

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;是一个说明触发触发器的操作的字符串, 可以是INSERTUPDATE或者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',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,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,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();
原文链接:https://www.f2er.com/postgresql/193661.html

猜你在找的Postgre SQL相关文章