INSERT事件触发器函数

函数功能实现字段值t_trigger.nc值重写。

teledb=# CREATE TABLE t_trigger
teledb-# (
teledb(#     id integer NOT NULL,
teledb(#     nc text NOT NULL
teledb(# );
CREATE TABLE
teledb=# CREATE OR REPLACE FUNCTION t_trigger_insert_trigger_func() RETURNS trigger AS 
teledb-# $$
teledb$# BEGIN
teledb$#     IF NEW.nc = '' THEN  
teledb$#         NEW.nc = 'teledb_pg_' || random()::text;
teledb$#     END IF;
teledb$#     RETURN NEW;
teledb$# END;
teledb$# $$ 
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# CREATE TRIGGER t_trigger_insert_trigger BEFORE INSERT ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_insert_trigger_func();
CREATE TRIGGER
teledb=# INSERT INTO t_trigger values(1,'');
INSERT 0 1
teledb=# SELECT * FROM t_trigger ;
 id |           nc        
----+-------------------------
  1 | teledb_pg_0.426093454472721
(1 row)

注意使用BEFORE，不能使用AFTER，否则重写失效。

UPDATE 事件触发器函数

不准许更新t_trigger.nc字段值为teledb_pg。

teledb=# CREATE OR REPLACE FUNCTION t_trigger_update_trigger_func() RETURNS trigger AS 
teledb-# $$
teledb$# BEGIN
teledb$#     --不准许t_trigger.nc值为 teledb_pg
teledb$#     IF NEW.nc = 'teledb_pg' THEN  
teledb$#         NEW.nc = OLD.nc ;
teledb$#     END IF;  
teledb$#     RETURN NEW;
teledb$# END;
teledb$# $$ 
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# CREATE TRIGGER t_trigger_update_trigger BEFORE UPDATE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_update_trigger_func();
CREATE TRIGGER
teledb=# UPDATE t_trigger SET nc='teledb_pg' WHERE id=1;
UPDATE 1
teledb=# SELECT * FROM t_trigger ;
 id |           nc          
----+-------------------------
  1 | teledb_pg_0.426093454472721
(1 row)

teledb=# 

DELETE事件触发器函数

限制teledb_pg记录不能被删除。

teledb=# CREATE OR REPLACE FUNCTION t_trigger_delete_trigger_func() RETURNS trigger AS 
teledb-# $$
teledb$# BEGIN
teledb$#     --不准许t_trigger.nc值为 teledb_pg
teledb$#     IF OLD.nc = 'teledb_pg' THEN  
teledb$#         RETURN NULL;
teledb$#         --RAISE EXCEPTION 'teledb_pg不能被删除';
teledb$#     END IF;  
teledb$#     RETURN OLD;
teledb$# END;
teledb$# $$ 
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# CREATE TRIGGER t_trigger_delete_trigger BEFORE DELETE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_delete_trigger_func();
CREATE TRIGGER
teledb=# INSERT INTO t_trigger VALUES(2,'teledb_pg');
INSERT 0 1
teledb=# SELECT * FROM t_trigger ;
 id |           nc          
----+-------------------------
  1 | teledb_pg_0.426093454472721
  2 | teledb_pg
(2 rows)
teledb=# DELETE FROM t_trigger WHERE id=2;
DELETE 0
teledb=# SELECT * FROM t_trigger ;
 id |           nc          
----+-------------------------
  1 | teledb_pg_0.426093454472721
  2 | teledb_pg
(2 rows)

删除触发器

teledb=# drop TRIGGER t_trigger_insert_trigger on t_trigger;
DROP TRIGGER

触发器使用限制

分区表，冷热分区表和复制表不支持使用触发器。