PostgreSQL 9.3 新增事件触发器，事件触发器为全局触发器，影响范围为指定的某个库，并且可以捕获
DDL 事件，而传统的触发器是基于表级别，并且只能捕获 DML 事件。
一 基本知识
--1.1 事件触发器语法
Name
CREATE EVENT TRIGGER -- define a new event trigger
Synopsis
CREATE EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE function_name()
Description
CREATE EVENT TRIGGER creates a new event trigger. Whenever the designated event occurs and the
WHEN condition associated with the trigger, if any, is satisfied, the trigger function will be
executed. For a general introduction to event triggers, see Chapter 37. The user who creates an
event triggerbecomes its owner.
--1.2 关于触发器 EVENT
当指定 EVENT 发生时触发器才会被触发，目前支持三种 EVENT: ddl_command_start，ddl_command_end ，
sql_drop。
1 ddl_command_start : 在 CREATE, ALTER, 或 DROP 命令执行之前触发，例外的情况是: 这不会在共享
对象上触发，例如 database，roles， tablespace。
2 ddl_command_end： ddl_command_end 事件在以类似同样命令执行后触发。
3 sql_drop: sql_drop 事件在 ddl_command_end 触发之前触发，要查看删除的数据库对像，可以通过函数
pg_event_trigger_dropped_objects() 查看，当数据库对像在 catalog 表中被删除时，触发器被触发。
--1.3 event trigger 使用场景
1 逻辑复制
2 审计
3 限制用户的DDL 命令
备注：这里列出主要的应用场景，可能还有更多。
以上是 PostgreSQL 9.3 事件触发器的基本知识，接下来通过一个示例进一步了解 EVENT trigger。
二 示例：审计数据库的 drop 命令
这个示例将创建 sql_drop 事件触发器，用来记录指定数据库中 drop 命令历史记录。
--2.1 创建 drop 命令审计表
|
[pg93@redhatB ~]$ psql francs postgres
psql (9.3beta1) Type "help" for help. francs=# create table tbl_ddl_drop_log ( francs(# login_role text, francs(# db_name character varying(64), francs(# client_ip inet, francs(# ddl_type character varying(32), francs(# schema_name text, francs(# object_type text, francs(# object_name text, francs(# object_identity text, francs(# drop_time timestamp with time zone francs(# ); CREATE TABLE francs=# grant insert on tbl_ddl_drop_log to francs; GRANT |
--2.2 创建 sql_drop 触发器函数
|
francs=# CREATE OR REPLACE FUNCTION
fun_log_drop_command()
francs-# RETURNS event_trigger LANGUAGE plpgsql AS $$ francs$# DECLARE francs$# obj record; francs$# BEGIN francs$# FOR obj IN (SELECT * FROM pg_event_trigger_dropped_objects() t where t.object_type francs$# in ('table','sequence','index','function','view')) francs$# LOOP francs$# insert into tbl_ddl_drop_log ( francs$# login_role, francs$# db_name, francs$# client_ip, francs$# ddl_type, francs$# schema_name, francs$# object_type, francs$# object_name, francs$# object_identity, francs$# drop_time) francs$# values ( francs$# current_user, francs$# current_database(), francs$# inet_client_addr(), francs$# tg_tag, francs$# obj.schema_name, francs$# obj.object_type, francs$# obj.object_name, francs$# obj.object_identity, francs$# now()); francs$# END LOOP; francs$# END; francs$# $$; CREATE FUNCTION |
pg_event_trigger_dropped_objects() 返回被删除的数据库对像，我这里做了过滤，
关于 pg_event_trigger_dropped_objects 的返回类型，参考文本末尾的附。
--2.3 创建触发器
|
francs=# CREATE EVENT TRIGGER trg_log_drop_command ON
sql_drop
francs-# EXECUTE PROCEDURE fun_log_drop_command(); CREATE EVENT TRIGGER francs=# \dy List of event triggers Name | Event | Owner | Enabled | Procedure | Tags ----------------------+----------+----------+---------+----------------------+------ trg_log_drop_command | sql_drop | postgres | enabled | fun_log_drop_command | (1 row) |
--2.4 测试
--2.4.1 创建测试表和测试函数
|
[pg91@redhat6 ~]$ psql -h 192.168.1.36 -p 1925 francs francs
Password for user francs: psql (9.1.2, server 9.3beta1) WARNING: psql version 9.1, server version 9.3. Some psql features might not work. Type "help" for help. francs=> create table test_trigger(id serial primary key); CREATE TABLE francs=> CREATE or replace FUNCTION fun_ins_test_1(i int4) RETURNS INTEGER AS $$ francs$> BEGIN francs$> return $1::text; francs$> END; francs$> $$ LANGUAGE 'plpgsql'; CREATE FUNCTION |
备注：这步在客户端（192.168.1.35） 上操作。
--2.4.2 删除测试表和测试函数
|
francs=> drop table test_trigger;
DROP TABLE francs=> drop function fun_ins_test_1(i int4); DROP FUNCTION |
之后，接下来看下审计表。
--2.4.3 服务端查看审计表
备注：这步在服务端（192.168.1.36） 上操作，测试成功，以上只是简单的演示 EVENT TRIGGER，生产
上的审计比这复杂得多。
三 附: Event Trigger Functions
pg_event_trigger_dropped_objects returns a list of all object dropped by the command in whose
sql_drop event it is called. If called in any other context, pg_event_trigger_dropped_objects raises
an error. pg_event_trigger_dropped_objects returns the following columns:
| Name | Type | Description |
|---|---|---|
| classid | Oid | OID of catalog the object belonged in |
| objid | Oid | OID the object had within the catalog |
| objsubid | int32 | Object sub-id (e.g. attribute number for columns) |
| object_type | text | Type of the object |
| schema_name | text | Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied. |
| object_name | text | Name of the object, if the combination of schema and name can be used as an unique identifier for the object; otherwise NULL. No quoting is applied, and name is never schema-qualified. |
| object_identity | text | Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary. |
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有