PostgreSQL 事件触发器
创建触发器的步骤分两步,a. 创建触发器函数,b. 编写触发语句;创建事件触发器的步骤也是一样,分为两步。 1. 简介: 2. 语法: postgres=# h create event trigger
Command: CREATE EVENT TRIGGER
Description: define a new event trigger
Syntax:
CREATE EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [,... ]) [ AND ... ] ]
EXECUTE PROCEDURE function_name()
name : 事件触发器的名称。
filter_variable: 过滤事件的变量名称.这将限制它所支持的事件的一个子集去触发该触发器.现在仅支持filter_variable值为TAG. filter_value: drop table,alter table,drop function.. (参考: http://www.postgresql.org/docs/current/static/event-trigger-matrix.html) 3. 事件触发函数pg_event_trigger_dropped_objects():
CREATE FUNCTION test_event_trigger_for_drops() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record;
BEGIN FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP RAISE NOTICE '% dropped object: % %.% %',tg_tag,obj.object_type,obj.schema_name,obj.object_name,obj.object_identity;
END LOOP;
END $$;
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON sql_drop
EXECUTE PROCEDURE test_event_trigger_for_drops();
新建一个表,然后删除: postgres=# create table tb10 (id integer);
CREATE TABLE postgres=# postgres=# drop table tb10;
NOTICE: DROP TABLE dropped object: table public.tb10 public.tb10 NOTICE: DROP TABLE dropped object: type public.tb10 public.tb10 NOTICE: DROP TABLE dropped object: type public._tb10 public.tb10[] DROP TABLE
新建一个function,然后删除: postgres=# create function ff() returns void as $$ postgres$# begin postgres$# end postgres$# $$language plpgsql;
CREATE FUNCTION postgres=# postgres=# drop function ff();
NOTICE: tg_tag: DROP FUNCTION; object_type: function
NOTICE: DROP FUNCTION dropped object: function public.<NULL> public.ff() DROP FUNCTION
-- 先删除刚刚之前创建的event trigger
postgres=# drop event trigger test_event_trigger_for_drops;
DROP EVENT TRIGGER postgres=#
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON sql_drop WHEN TAG in('drop table')
EXECUTE PROCEDURE test_event_trigger_for_drops();
-- 新建表和function
postgres=# create table tb10(id integer);
CREATE TABLE postgres=# create function ff()returns void as $$ begin end $$language plpgsql;
CREATE FUNCTION -- 测试删除 postgres=# drop function ff();
DROP FUNCTION postgres=# postgres=# drop table tb10;
NOTICE: DROP TABLE dropped object: table public.tb10 public.tb10 NOTICE: DROP TABLE dropped object: type public.tb10 public.tb10 NOTICE: DROP TABLE dropped object: type public._tb10 public.tb10[] DROP TABLE
可以看到,只有drop table的时候被拦截了。 5. 查看所有触发器 dy: postgres=# dy
List of event triggers
Name | Event | Owner | Enabled | Procedure | Tags ------------------------------+----------+----------+---------+------------------------------+------------ test_event_trigger_for_drops | sql_drop | postgres | enabled | test_event_trigger_for_drops | DROP TABLE trg_log_drop_command | sql_drop | postgres | enabled | fun_log_drop_command | (2 rows)
参考: (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |