PostgreSQL外键自动创建约束触发器
约束语法
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
这里的event可以是下列之一：
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
当CONSTRAINT选项被指定，这个命令会创建一个约束触发器。这和一个常规触发器相同，不过触发该触发器的时机可以使用SET CONSTRAINTS调整。约束触发器必须是表上的AFTER ROW触发器。它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。在后一种情况中，它们被称作是被延迟。一个待处理的延迟触发器的引发也可以使用SET CONSTRAINTS立即强制发生。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。
REFERENCING选项启用对传递关系的收集，传递关系是包括被当前SQL语句插入、删除或者修改的行的行集合。这个特性让触发器能看到该语句做的事情的全局视图，而不是一次只看到一行。仅对非约束触发器的AFTER触发器允许这个选项。此外，如果触发器是一个UPDATE触发器，则它不能指定column_name列表。OLD TABLE仅可以被指定一次，并且只能为在UPDATE或DELETE事件上引发的触发器指定，它创建的传递关系包含有该语句更新或删除的所有行的前映像。类似地，NEW TABLE仅可以被指定一次，并且只能为在UPDATE或INSERT事件上引发的触发器指定，它创建的传递关系包含有该语句更新或插入的所有行的后映像。
SELECT不修改任何行，因此你无法创建SELECT触发器。规则和视图可以为需要SELECT触发器的问题提供可行的解决方案。
外键示例
默认选项
set search_path to test;
drop table if exists author,page;
create table author(
author_id int primary key,
firstname varchar(50),
lastname varchar(50)
);
create table page (
page_id serial primary key,
title varchar(255),
content text,
author_id int,
foreign key (author_id) references author (author_id)
);
查询触发器信息
select substring(pg_get_triggerdef(oid) from 'ON\s+(\S+)') as table_name,
substring(pg_get_triggerdef(oid) from 'EXECUTE\s+FUNCTION\s+(\S+)') as function_name,
pg_get_triggerdef(oid) as trigger_definition
from pg_trigger;
-- 查询结果
table_name | function_name | trigger_definition
-------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test.author | "RI_FKey_noaction_del"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_a_24594" AFTER DELETE ON test.author FROM page NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_noaction_del"()
test.author | "RI_FKey_noaction_upd"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_a_24595" AFTER UPDATE ON test.author FROM page NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_noaction_upd"()
test.page | "RI_FKey_check_ins"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_24596" AFTER INSERT ON test.page FROM author NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()
test.page | "RI_FKey_check_upd"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_24597" AFTER UPDATE ON test.page FROM author NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"()
(4 rows)
如上可以看出默认的外键属性是on delete no action on update no action
on delete cascade on update cascade
set search_path to test;
drop table if exists author,page;
create table author(
author_id int primary key,
firstname varchar(50),
lastname varchar(50)
);
create table page (
page_id serial primary key,
title varchar(255),
content text,
author_id int,
foreign key (author_id) references author (author_id) on delete cascade on update cascade
);
查询触发器信息
select substring(pg_get_triggerdef(oid) from 'ON\s+(\S+)') as table_name,
substring(pg_get_triggerdef(oid) from 'EXECUTE\s+FUNCTION\s+(\S+)') as function_name,
pg_get_triggerdef(oid) as trigger_definition
from pg_trigger;
-- 查询结果
table_name | function_name | trigger_definition
-------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test.author | "RI_FKey_cascade_del"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_a_24615" AFTER DELETE ON test.author FROM page NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_cascade_del"()
test.author | "RI_FKey_cascade_upd"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_a_24616" AFTER UPDATE ON test.author FROM page NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_cascade_upd"()
test.page | "RI_FKey_check_ins"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_24617" AFTER INSERT ON test.page FROM author NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()
test.page | "RI_FKey_check_upd"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_24618" AFTER UPDATE ON test.page FROM author NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"()
(4 rows)
约束延迟选项
deferrable initially deferred
set search_path to test;
drop table if exists author,page;
create table author(
author_id int primary key,
firstname varchar(50),
lastname varchar(50)
);
create table page (
page_id serial primary key,
title varchar(255),
content text,
author_id int,
foreign key (author_id) references author (author_id) on delete cascade on update cascade deferrable initially deferred
);
查询触发器信息
select substring(pg_get_triggerdef(oid) from 'ON\s+(\S+)') as table_name,
substring(pg_get_triggerdef(oid) from 'EXECUTE\s+FUNCTION\s+(\S+)') as function_name,
pg_get_triggerdef(oid) as trigger_definition
from pg_trigger;
-- 查询结果
table_name | function_name | trigger_definition
-------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test.author | "RI_FKey_cascade_del"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_a_24636" AFTER DELETE ON test.author FROM page NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_cascade_del"()
test.author | "RI_FKey_cascade_upd"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_a_24637" AFTER UPDATE ON test.author FROM page NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_cascade_upd"()
test.page | "RI_FKey_check_ins"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_24638" AFTER INSERT ON test.page FROM author DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()
test.page | "RI_FKey_check_upd"() | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_24639" AFTER UPDATE ON test.page FROM author DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"()
(4 rows)
如上可以看出延迟选项只针对外键，而不是外键delete和update的选项。
浙公网安备 33010602011771号