【PostgreSQL-9.6.3】事件触发器
当预定的事件发生时，事件触发器就会被触发。由于事件触发器设计的权限比较大，所以只有超级用户才能创建和修改触发器。
1. 事件触发器支持的事件分三类：ddl_command_start, ddl_command_end 和 sql_drop。
（1）ddl_command_start：在DDL开始前触发；
（2）ddl_command_end：在DDl结束后触发；
（3）sql_drop：删除一个数据库对象前被触发，其中删除的数据库对象详细信息，可以通过pg_event_trigger_dropped_objects()函数记录下来。
| 列名称 | 列类型 | 列描述 |
| classid | Oid | 对象所在目录的Oid |
| objjd | Oid | 数据库对象的Oid |
| objsubid | int32 | 数据库对象的子对象 （如：列） |
| object_type | text | 数据库对象的类型 |
| schema_name | text | 数据库对象的模式名 |
| object_name | text | 数据库对象的名称 |
| object_identify | text | 数据库对象的标识符 |
2. 各种DDL操作会触发的事件列表：
| 命令标记 | ddl_command_start | ddl_command_end | sql_drop |
|---|---|---|---|
| ALTER AGGREGATE | X | X | - |
| ALTER COLLATION | X | X | - |
| ALTER CONVERSION | X | X | - |
| ALTER DOMAIN | X | X | - |
| ALTER EXTENSION | X | X | - |
| ALTER FOREIGN DATA WRAPPER | X | X | - |
| ALTER FOREIGN TABLE | X | X | X |
| ALTER FUNCTION | X | X | - |
| ALTER LANGUAGE | X | X | - |
| ALTER OPERATOR | X | X | - |
| ALTER OPERATOR CLASS | X | X | - |
| ALTER OPERATOR FAMILY | X | X | - |
| ALTER SCHEMA | X | X | - |
| ALTER SEQUENCE | X | X | - |
| ALTER SERVER | X | X | - |
| ALTER TABLE | X | X | X |
| ALTER TEXT SEARCH CONFIGURATION | X | X | - |
| ALTER TEXT SEARCH DICTIONARY | X | X | - |
| ALTER TEXT SEARCH PARSER | X | X | - |
| ALTER TEXT SEARCH TEMPLATE | X | X | - |
| ALTER TRIGGER | X | X | - |
| ALTER TYPE | X | X | - |
| ALTER USER MAPPING | X | X | - |
| ALTER VIEW | X | X | - |
| CREATE AGGREGATE | X | X | - |
| CREATE CAST | X | X | - |
| CREATE COLLATION | X | X | - |
| CREATE CONVERSION | X | X | - |
| CREATE DOMAIN | X | X | - |
| CREATE EXTENSION | X | X | - |
| CREATE FOREIGN DATA WRAPPER | X | X | - |
| CREATE FOREIGN TABLE | X | X | - |
| CREATE FUNCTION | X | X | - |
| CREATE INDEX | X | X | - |
| CREATE LANGUAGE | X | X | - |
| CREATE OPERATOR | X | X | - |
| CREATE OPERATOR CLASS | X | X | - |
| CREATE OPERATOR FAMILY | X | X | - |
| CREATE RULE | X | X | - |
| CREATE SCHEMA | X | X | - |
| CREATE SEQUENCE | X | X | - |
| CREATE SERVER | X | X | - |
| CREATE TABLE | X | X | - |
| CREATE TABLE AS | X | X | - |
| CREATE TEXT SEARCH CONFIGURATION | X | X | - |
| CREATE TEXT SEARCH DICTIONARY | X | X | - |
| CREATE TEXT SEARCH PARSER | X | X | - |
| CREATE TEXT SEARCH TEMPLATE | X | X | - |
| CREATE TRIGGER | X | X | - |
| CREATE TYPE | X | X | - |
| CREATE USER MAPPING | X | X | - |
| CREATE VIEW | X | X | - |
| DROP AGGREGATE | X | X | X |
| DROP CAST | X | X | X |
| DROP COLLATION | X | X | X |
| DROP CONVERSION | X | X | X |
| DROP DOMAIN | X | X | X |
| DROP EXTENSION | X | X | X |
| DROP FOREIGN DATA WRAPPER | X | X | X |
| DROP FOREIGN TABLE | X | X | X |
| DROP FUNCTION | X | X | X |
| DROP INDEX | X | X | X |
| DROP LANGUAGE | X | X | X |
| DROP OPERATOR | X | X | X |
| DROP OPERATOR CLASS | X | X | X |
| DROP OPERATOR FAMILY | X | X | X |
| DROP OWNED | X | X | X |
| DROP RULE | X | X | X |
| DROP SCHEMA | X | X | X |
| DROP SEQUENCE | X | X | X |
| DROP SERVER | X | X | X |
| DROP TABLE | X | X | X |
| DROP TEXT SEARCH CONFIGURATION | X | X | X |
| DROP TEXT SEARCH DICTIONARY | X | X | X |
| DROP TEXT SEARCH PARSER | X | X | X |
| DROP TEXT SEARCH TEMPLATE | X | X | X |
| DROP TRIGGER | X | X | X |
| DROP TYPE | X | X | X |
| DROP USER MAPPING | X | X | X |
| DROP VIEW | X | X | X |
| SELECT INTO | X | X | - |
3. 创建事件触发器的语法
CREATE EVENT TRIGGER name
ON EVENT
[ WHEN filter_variable IN ( filter_value [ , ... ]) [ AND ... ] ]
EXECUTE PROCEDURE function_name ()
4. 事件触发器示例
（1）禁止所有DDL操作
--创建触发器函数 create or replace function abort_any_command() returns event_trigger language plpgsql as $$ begin raise exception 'command % is disabled ',tg_tag; end; $$;
--创建触发器 create event trigger abort_ddl on ddl_command_start execute procedure abort_any_command();
--测试触发器 test=# drop table emp; ERROR: command DROP TABLE is disabled CONTEXT: PL/pgSQL function abort_any_command() line 3 at RAISE test=# create table emp01 (x int); ERROR: command CREATE TABLE is disabled CONTEXT: PL/pgSQL function abort_any_command() line 3 at RAISE
test=# truncate table emp; --注意：truncate是在普通触发器中触发，在事件触发器中不会触发。 TRUNCATE TABLE
--恢复DDL正常操作
test=# alter event trigger abort_ddl disable;
ALTER EVENT TRIGGER
（2）审计记录所有drop操作
--创建审计记录表 create table log_drop_objects( op_time timestamp, --操作执行时间 ddl_tag text, --执行的ddl操作 classid Oid, objid Oid, objsubid OID, object_type text, schema_name text, object_name text, object_identify text ); --创建触发器函数 create function event_trigger_log_drops() returns event_trigger language plpgsql as $$ declare obj record; begin insert into log_drop_objects select now(),tg_tag,classid,objid,objsubid, object_type,schema_name,object_name, object_identity from pg_event_trigger_dropped_objects(); end $$; --创建触发器 create event trigger event_trigger_log_drops on sql_drop execute procedure event_trigger_log_drops(); --测试触发器 test=# alter table emp drop column salary; ALTER TABLE test=# test=# select ddl_tag,object_type,object_name,object_identity from log_drop_objects; ddl_tag | object_type | object_name | object_identity -------------+--------------+-------------+------------------- ALTER TABLE | table column | | public.emp.salary (1 row) test=# test=# drop table emp; DROP TABLE test=# test=# select ddl_tag,object_type,object_name,object_identity from log_drop_objects; ddl_tag | object_type | object_name | object_identity -------------+--------------+----------------------+------------------------------- ALTER TABLE | table column | | public.emp.salary DROP TABLE | table | emp | public.emp DROP TABLE | type | emp | public.emp DROP TABLE | type | _emp | public.emp[] DROP TABLE | toast table | pg_toast_16461 | pg_toast.pg_toast_16461 DROP TABLE | index | pg_toast_16461_index | pg_toast.pg_toast_16461_index DROP TABLE | type | pg_toast_16461 | pg_toast.pg_toast_16461 DROP TABLE | trigger | | emp_audit on public.emp (8 rows)
5. 修改事件触发器
ALTER EVENT TRIGGER name DISABLE ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ] ALTER EVENT TRIGGER name OWNER TO new_owner ALTER EVENT TRIGGER name RENAME TO new_name
The End！
2017-08-20
