Postgresql - Trigger Procedures

最新推荐文章于 2024-09-30 08:07:13 发布

|ChuckChen|

最新推荐文章于 2024-09-30 08:07:13 发布

阅读量737

点赞数

分类专栏： Postgresql 文章标签： Postgresql trigger 触发器

本文链接：https://blog.csdn.net/chuckchen1222/article/details/82463402

版权

Postgresql 专栏收录该内容

142 篇文章 8 订阅

订阅专栏

本文翻译自官方文档：

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

=======================================================

PL/PGSQL可用于定义数据更改或数据库事件的触发程序。使用CREATE FUNCTION命令创建触发器过程，将其声明为没有参数和返回类型的触发器（用于数据更改触发器）或event_trigger.（用于数据库事件触发器）的函数。自动定义特定的本地变量名为TG_something，以描述触发调用的条件。

1. Triggers on Data Changes

数据更改触发器被声明为没有参数和返回类型触发器的函数。注意，即使函数希望接收CREATE TRIGGER中指定的一些参数，也必须不带参数地声明该函数——这些参数是通过TG_ARGV传递的。

当PL/PGSQL函数被称为触发器时，在顶层块中会自动创建几个特殊的变量。

NEW	数据类型记录；在行级触发器中保存用于插入/更新操作的新数据库行的变量。此变量在语句级触发器和删除操作中未赋值。
OLD	数据类型记录；变量保存旧数据库行，用于行级触发器中的更新/删除操作。此变量在语句级触发器和插入操作中未赋值。
TG_NAME	数据类型名称；包含实际触发的触发器名称的变量。
TG_WHEN	数据类型文本；取决于触发器定义的前、后或替代字符串。
TG_LEVEL	数据类型文本；取决于触发器定义的任一行或语句的字符串。
TG_OP	数据类型文本；插入、更新、删除或截断字符串，用于触发触发器的哪个操作。
TG_RELID	数据类型OID；引发触发器调用的表的对象ID。
TG_RELNAME	数据类型名称；引发触发器调用的表的名称。现在不赞成使用，并且可能在未来的版本中消失。使用TG_TABLE_NAME代替。
TG_TABLE_NAME	数据类型名称；引发触发器调用的表的名称。
TG_TABLE_SCHEMA	数据类型名称；引发触发器调用的表的架构名称。
TG_NARGS	数据类型整数；CREATE TRIGGER语句中的触发器过程的参数数目。
TG_ARGV[]	文本的数据类型数组；来自CREATE TRIGGER语句的参数。指数为0。无效的索引（小于0或大于或等于tg_nargs）导致空值。

触发器函数必须返回NULL或记录/行值，该值具有触发器触发的表的结构。

BEFORE触发的行级触发器可以返回null以通知触发器管理器跳过该行的其余操作（即，不触发后续触发器，并且该行不发生INSERT/UPDATE/DELETE）。如果返回非空值，则操作将继续使用该行值。返回与原始值不同的行值更改将插入或更新的行。因此，如果触发器函数希望触发动作正常成功，而不改变行值，则必须返回NEW（或与其相等的值）。要更改要存储的行，可以直接在NEW中替换单个值并返回修改后的NEW，或者构建要返回的完整新记录/行。对于DELETE上的事前触发，返回的值没有直接影响，但是必须是非空的，才能继续触发操作。注意，删除触发器中的NULL为NULL，因此返回通常是不明智的。删除触发器中惯用的习惯用法是返回旧的。

INSTEAD OF触发器（它总是行级触发器，并且只能用于视图）可以返回null以表示它们没有执行任何更新，并且应该跳过该行的其余操作（即，不触发后续触发器，并且该行不计数）。S对周围插入/更新/删除的影响状态。否则，应返回非空值，以指示触发器执行所请求的操作。对于INSERT和UPDATE操作，返回值应该是NEW，触发器函数可以修改该值以支持INSERT RETURNING和UPDATE RETURNING（这也将影响传递给任何后续触发器的行值，或者传递给INSERT语句中的特殊EXCLUDED别名引用）。使用ON冲突DO更新子句。对于删除操作，返回值应该是旧的。

AFTER触发的行级触发器或BEFORE或AFTER触发的语句级触发器的返回值总是被忽略；它也可能是空的。但是，这些类型的触发器中的任何一个仍然可能通过引发错误中止整个操作。

Example:

mytest=# CREATE TABLE emp (

mytest(# empname text,

mytest(# salary integer,

mytest(# last_date timestamp,

mytest(# last_user text

mytest(# );

CREATE TABLE

mytest=# CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$

mytest$# BEGIN

mytest$# -- Check that empname and salary are given

mytest$# IF NEW.empname IS NULL THEN

mytest$# RAISE EXCEPTION 'empname cannot be null';

mytest$# END IF;

mytest$# IF NEW.salary IS NULL THEN

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

mytest$# END IF;

mytest$# -- Who works for us when they must pay for it?

mytest$# IF NEW.salary < 0 THEN

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

mytest$# END IF;

mytest$# -- Remember who changed the payroll when

mytest$# NEW.last_date := current_timestamp;

mytest$# NEW.last_user := current_user;

mytest$# RETURN NEW;

mytest$# END;

mytest$# $emp_stamp$ LANGUAGE plpgsql;

CREATE FUNCTION

mytest=# CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp

mytest-# FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

CREATE TRIGGER

mytest=# INSERT INTO emp VALUES('xxx','1000',now(),'zzz');

INSERT 0 1

mytest=# INSERT INTO emp(empname,salary,last_date,last_user) VALUES('xxx','1000',now(),'zzz');

INSERT 0 1

mytest=# INSERT INTO emp(salary,last_date,last_user) VALUES('1000',now(),'zzz');

ERROR: empname cannot be null

CONTEXT: PL/pgSQL function emp_stamp() line 5 at RAISE

mytest=# INSERT INTO emp(empname,salary,last_date,last_user) VALUES('xxx','-1000',now(),'zzz');

ERROR: xxx cannot have a negative salary

CONTEXT: PL/pgSQL function emp_stamp() line 13 at RAISE

mytest=# INSERT INTO emp(empname,salary) VALUES('xxx','1000');

INSERT 0 1

mytest=# select * from emp ;

empname | salary | last_date | last_user

---------+--------+----------------------------+-----------

xxx | 1000 | 2018-09-05 19:13:40.164249 | mytest

xxx | 1000 | 2018-09-05 19:14:08.151029 | mytest

xxx | 1000 | 2018-09-05 19:15:05.935967 | mytest

(3 rows)

---------------------------------------------------------------------------

mytest=# drop table emp cascade;

DROP TABLE

mytest=# drop table emp_audit;

DROP TABLE

mytest=# CREATE TABLE emp (

mytest(# empname text NOT NULL,

mytest(# salary integer

mytest(# );

CREATE TABLE

mytest=# CREATE TABLE emp_audit(

mytest(# operation char(1) NOT NULL,

mytest(# stamp timestamp NOT NULL,

mytest(# userid text NOT NULL,

mytest(# empname text NOT NULL,

mytest(# salary integer

mytest(# );

CREATE TABLE

mytest=# CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$

mytest$# BEGIN

mytest$# --

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

mytest$# -- making use of the special variable TG_OP to work out the operation.

mytest$# --

mytest$# IF (TG_OP = 'DELETE') THEN

mytest$# INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;

mytest$# ELSIF (TG_OP = 'UPDATE') THEN

mytest$# INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;

mytest$# ELSIF (TG_OP = 'INSERT') THEN

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

mytest$# END IF;

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

mytest$# END;

mytest$# $emp_audit$ LANGUAGE plpgsql;

CREATE FUNCTION

mytest=# CREATE TRIGGER emp_audit

mytest-# AFTER INSERT OR UPDATE OR DELETE ON emp

mytest-# FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

CREATE TRIGGER

mytest=# INSERT INTO emp VALUES ('xxx',1000);

INSERT 0 1

mytest=# INSERT INTO emp VALUES ('yyy',2000);

INSERT 0 1

mytest=# update emp set salary = 3000 where empname = 'xxx';

UPDATE 1

mytest=# delete from emp where empname='yyy';

DELETE 1

mytest=# select * from emp_audit;

operation | stamp | userid | empname | salary

-----------+----------------------------+--------+---------+--------

I | 2018-09-06 14:14:50.114404 | mytest | xxx | 1000

I | 2018-09-06 14:15:16.25861 | mytest | yyy | 2000

U | 2018-09-06 14:15:58.364829 | mytest | xxx | 3000

D | 2018-09-06 14:16:28.261995 | mytest | yyy | 2000

(4 rows)

---------------------------------------------------------------------------

mytest=# drop table emp;

DROP TABLE

mytest=# drop table emp_audit;

DROP TABLE

mytest=# CREATE TABLE emp (

mytest(# empname text PRIMARY KEY,

mytest(# salary integer

mytest(# );

CREATE TABLE

mytest=# CREATE TABLE emp_audit(

mytest(# operation char(1) NOT NULL,

mytest(# userid text NOT NULL,

mytest(# empname text NOT NULL,

mytest(# salary integer,

mytest(# stamp timestamp NOT NULL

mytest(# );

CREATE TABLE

mytest=# CREATE VIEW emp_view AS

mytest-# SELECT e.empname,

mytest-# e.salary,

mytest-# max(ea.stamp) AS last_updated

mytest-# FROM emp e

mytest-# LEFT JOIN emp_audit ea ON ea.empname = e.empname

mytest-# GROUP BY 1, 2;

CREATE VIEW

mytest=# CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$

mytest$# BEGIN

mytest$# --

mytest$# -- Perform the required operation on emp, and create a row in emp_audit

mytest$# -- to reflect the change made to emp.

mytest$# --

mytest$# IF (TG_OP = 'DELETE') THEN

mytest$# DELETE FROM emp WHERE empname = OLD.empname;

mytest$# IF NOT FOUND THEN RETURN NULL; END IF;

mytest$#

mytest$# OLD.last_updated = now();

mytest$# INSERT INTO emp_audit VALUES('D', user, OLD.*);

mytest$# RETURN OLD;

mytest$# ELSIF (TG_OP = 'UPDATE') THEN

mytest$# UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;

mytest$# IF NOT FOUND THEN RETURN NULL; END IF;

mytest$#

mytest$# NEW.last_updated = now();

mytest$# INSERT INTO emp_audit VALUES('U', user, NEW.*);

mytest$# RETURN NEW;

mytest$# ELSIF (TG_OP = 'INSERT') THEN

mytest$# INSERT INTO emp VALUES(NEW.empname, NEW.salary);

mytest$#

mytest$# NEW.last_updated = now();

mytest$# INSERT INTO emp_audit VALUES('I', user, NEW.*);

mytest$# RETURN NEW;

mytest$# END IF;

mytest$# END;

mytest$# $$ LANGUAGE plpgsql;

CREATE FUNCTION

mytest=# CREATE TRIGGER emp_audit

mytest-# INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view

mytest-# FOR EACH ROW EXECUTE PROCEDURE update_emp_view();

CREATE TRIGGER

mytest=# insert into emp_view values ('aa',1000,now());

INSERT 0 1

mytest=# insert into emp_view values ('bb',2000,now());

INSERT 0 1

mytest=# delete from emp_view where empname ='aa';

DELETE 1

mytest=# update emp_view set salary =3000 where empname = 'bb';

UPDATE 1

mytest=# select * from emp_audit ;

operation | userid | empname | salary | stamp

-----------+--------+---------+--------+----------------------------

I | mytest | aa | 1000 | 2018-09-06 14:51:16.470616

I | mytest | bb | 2000 | 2018-09-06 14:51:23.005594

D | mytest | aa | 1000 | 2018-09-06 14:51:48.0245

U | mytest | bb | 3000 | 2018-09-06 14:52:28.080044

(4 rows)

---------------------------------------------------------------------------

mytest=# CREATE TABLE time_dimension (

mytest(# time_key integer NOT NULL,

mytest(# day_of_week integer NOT NULL,

mytest(# day_of_month integer NOT NULL,

mytest(# month integer NOT NULL,

mytest(# quarter integer NOT NULL,

mytest(# year integer NOT NULL

mytest(# );

CREATE TABLE

mytest=# CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE INDEX

mytest=# CREATE TABLE sales_fact (

mytest(# time_key integer NOT NULL,

mytest(# product_key integer NOT NULL,

mytest(# store_key integer NOT NULL,

mytest(# amount_sold numeric(12,2) NOT NULL,

mytest(# units_sold integer NOT NULL,

mytest(# amount_cost numeric(12,2) NOT NULL

mytest(# );

CREATE TABLE

mytest=# CREATE INDEX sales_fact_time ON sales_fact(time_key);

CREATE INDEX

mytest=# CREATE TABLE sales_summary_bytime (

mytest(# time_key integer NOT NULL,

mytest(# amount_sold numeric(15,2) NOT NULL,

mytest(# units_sold numeric(12) NOT NULL,

mytest(# amount_cost numeric(15,2) NOT NULL

mytest(# );

CREATE TABLE

mytest=# CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

CREATE INDEX

mytest=# CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER

mytest-# AS $maint_sales_summary_bytime$

mytest$# DECLARE

mytest$# delta_time_key integer;

mytest$# delta_amount_sold numeric(15,2);

mytest$# delta_units_sold numeric(12);

mytest$# delta_amount_cost numeric(15,2);

mytest$# BEGIN

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

mytest$# IF (TG_OP = 'DELETE') THEN

mytest$# delta_time_key = OLD.time_key;

mytest$# delta_amount_sold = -1 * OLD.amount_sold;

mytest$# delta_units_sold = -1 * OLD.units_sold;

mytest$# delta_amount_cost = -1 * OLD.amount_cost;

mytest$# ELSIF (TG_OP = 'UPDATE') THEN

mytest$#

mytest$# -- forbid updates that change the time_key -

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

mytest$# -- changes will be made).

mytest$# IF ( OLD.time_key != NEW.time_key) THEN

mytest$# RAISE EXCEPTION 'Update of time_key : % -> % not allowed',

mytest$# OLD.time_key, NEW.time_key;

mytest$# END IF;

mytest$#

mytest$# delta_time_key = OLD.time_key;

mytest$# delta_amount_sold = NEW.amount_sold - OLD.amount_sold;

mytest$# delta_units_sold = NEW.units_sold - OLD.units_sold;

mytest$# delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

mytest$#

mytest$# ELSIF (TG_OP = 'INSERT') THEN

mytest$#

mytest$# delta_time_key = NEW.time_key;

mytest$# delta_amount_sold = NEW.amount_sold;

mytest$# delta_units_sold = NEW.units_sold;

mytest$# delta_amount_cost = NEW.amount_cost;

mytest$#

mytest$# END IF;

mytest$#

mytest$# -- Insert or update the summary row with the new values.

mytest$# <<insert_update>>

mytest$# LOOP

mytest$# UPDATE sales_summary_bytime

mytest$# SET amount_sold = amount_sold + delta_amount_sold,

mytest$# units_sold = units_sold + delta_units_sold,

mytest$# amount_cost = amount_cost + delta_amount_cost

mytest$# WHERE time_key = delta_time_key;

mytest$#

mytest$# EXIT insert_update WHEN found;

mytest$#

mytest$# BEGIN

mytest$# INSERT INTO sales_summary_bytime (

mytest$# time_key,

mytest$# amount_sold,

mytest$# units_sold,

mytest$# amount_cost)

mytest$# VALUES (

mytest$# delta_time_key,

mytest$# delta_amount_sold,

mytest$# delta_units_sold,

mytest$# delta_amount_cost

mytest$# );

mytest$#

mytest$# EXIT insert_update;

mytest$#

mytest$# EXCEPTION

mytest$# WHEN UNIQUE_VIOLATION THEN

mytest$# -- do nothing

mytest$# END;

mytest$# END LOOP insert_update;

mytest$#

mytest$# RETURN NULL;

mytest$# END;

mytest$# $maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE FUNCTION

mytest=#

mytest=# CREATE TRIGGER maint_sales_summary_bytime

mytest-# AFTER INSERT OR UPDATE OR DELETE ON sales_fact

mytest-# FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

CREATE TRIGGER

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

INSERT 0 1

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

INSERT 0 1

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

INSERT 0 1

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

INSERT 0 1

mytest=# SELECT * FROM sales_summary_bytime;

time_key | amount_sold | units_sold | amount_cost

----------+-------------+------------+-------------

1 | 30.00 | 8 | 50.00

2 | 50.00 | 16 | 148.00

(2 rows)

mytest=# DELETE FROM sales_fact WHERE product_key = 1;

DELETE 1

mytest=# SELECT * FROM sales_summary_bytime;

time_key | amount_sold | units_sold | amount_cost

----------+-------------+------------+-------------

2 | 50.00 | 16 | 148.00

1 | 20.00 | 5 | 35.00

(2 rows)

mytest=# UPDATE sales_fact SET units_sold = units_sold * 2;

UPDATE 3

mytest=# SELECT * FROM sales_summary_bytime;

time_key | amount_sold | units_sold | amount_cost

----------+-------------+------------+-------------

1 | 20.00 | 10 | 35.00

2 | 50.00 | 32 | 148.00

(2 rows)

---------------------------------------------------------------------------

mytest=# drop table emp;

DROP TABLE

mytest=# drop table emp_audit ;

DROP TABLE

mytest=# CREATE TABLE emp (

mytest(# empname text NOT NULL,

mytest(# salary integer

mytest(# );

CREATE TABLE

mytest=# CREATE TABLE emp_audit(

mytest(# operation char(1) NOT NULL,

mytest(# stamp timestamp NOT NULL,

mytest(# userid text NOT NULL,

mytest(# empname text NOT NULL,

mytest(# salary integer

mytest(# );

CREATE TABLE

mytest=# CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$

mytest$# BEGIN

mytest$# --

mytest$# -- Create rows in emp_audit to reflect the operations performed on emp,

mytest$# -- making use of the special variable TG_OP to work out the operation.

mytest$# --

mytest$# IF (TG_OP = 'DELETE') THEN

mytest$# INSERT INTO emp_audit

mytest$# SELECT 'D', now(), user, o.* FROM old_table o;

mytest$# ELSIF (TG_OP = 'UPDATE') THEN

mytest$# INSERT INTO emp_audit

mytest$# SELECT 'U', now(), user, n.* FROM new_table n;

mytest$# ELSIF (TG_OP = 'INSERT') THEN

mytest$# INSERT INTO emp_audit

mytest$# SELECT 'I', now(), user, n.* FROM new_table n;

mytest$# END IF;

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

mytest$# END;

mytest$# $emp_audit$ LANGUAGE plpgsql;

CREATE FUNCTION

mytest=# CREATE TRIGGER emp_audit_ins

mytest-# AFTER INSERT ON emp

mytest-# REFERENCING NEW TABLE AS new_table

mytest-# FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

CREATE TRIGGER

mytest=# CREATE TRIGGER emp_audit_upd

mytest-# AFTER UPDATE ON emp

mytest-# REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table

mytest-# FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

CREATE TRIGGER

mytest=# CREATE TRIGGER emp_audit_del

mytest-# AFTER DELETE ON emp

mytest-# REFERENCING OLD TABLE AS old_table

mytest-# FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

CREATE TRIGGER

mytest=# insert into emp values ('aaa',1000);

INSERT 0 1

mytest=# insert into emp values ('bbb',2000);

INSERT 0 1

mytest=# update emp set salary = 3000 where empname='aaa';

UPDATE 1

mytest=# delete from emp where empname = 'bbb';

DELETE 1

mytest=# select * from emp_audit ;

operation | stamp | userid | empname | salary

-----------+----------------------------+--------+---------+--------

I | 2018-09-06 16:22:25.132921 | mytest | aaa | 1000

I | 2018-09-06 16:22:38.205117 | mytest | bbb | 2000

U | 2018-09-06 16:23:21.247002 | mytest | aaa | 3000

D | 2018-09-06 16:23:37.56697 | mytest | bbb | 2000

(4 rows)

2. Triggers on Events

PL/PGSQL可以用来定义事件触发器。PostgreSQL要求调用为事件触发器的过程必须声明为没有参数和event_trigger类型的函数。

当PL/pgSQL函数被作为事件触发器调用时，在top-level块中会自动创建几个特殊的变量。

TG_EVENT	数据类型文本；表示触发触发器的事件的字符串。
TG_TAG	数据类型文本；包含触发触发器的命令标签的变量。