当预定的事件发生时,事件触发器就会被触发。由于事件触发器设计的权限比较大,所以只有超级用户才能创建和修改触发器。
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操作会触发的事件列表:
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