INSERT 事件触发器函数
函数功能实现字段值 t_trigger.nc 值重写。
postgres=# CREATE TABLE t_triggerpostgres-# (postgres(# id integer NOT NULL,postgres(# nc text NOT NULLpostgres(# );CREATE TABLEpostgres=# CREATE OR REPLACE FUNCTION t_trigger_insert_trigger_func() RETURNS trigger ASpostgres-# $$postgres$# BEGINpostgres$# IF NEW.nc = '' THENpostgres$# NEW.nc = 'tdsql_pg_' || random()::text;postgres$# END IF;postgres$# RETURN NEW;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# CREATE TRIGGER t_trigger_insert_trigger BEFORE INSERT ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_insert_trigger_func();CREATE TRIGGERpostgres=# INSERT INTO t_trigger values(1,'');INSERT 0 1postgres=# SELECT * FROM t_trigger ;id | nc----+-------------------------1 | tdsql_pg_0.426093454472721(1 row)
注意使用 BEFORE,不能使用 AFTER,否则重写失效。
UPDATE 事件触发器函数
不准许更新 t_trigger.nc 字段值为 tdsql_pg。
postgres=# CREATE OR REPLACE FUNCTION t_trigger_update_trigger_func() RETURNS trigger ASpostgres-# $$postgres$# BEGINpostgres$# --不准许t_trigger.nc值为 tdsql_pgpostgres$# IF NEW.nc = 'tdsql_pg' THENpostgres$# NEW.nc = OLD.nc ;postgres$# END IF;postgres$# RETURN NEW;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# CREATE TRIGGER t_trigger_update_trigger BEFORE UPDATE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_update_trigger_func();CREATE TRIGGERpostgres=# UPDATE t_trigger SET nc='tdsql_pg' WHERE id=1;UPDATE 1postgres=# SELECT * FROM t_trigger ;id | nc----+-------------------------1 | tdsql_pg_0.426093454472721(1 row)postgres=#
DELETE 事件触发器函数
限制 tdsql_pg 记录不能被删除。
postgres=# CREATE OR REPLACE FUNCTION t_trigger_delete_trigger_func() RETURNS trigger ASpostgres-# $$postgres$# BEGINpostgres$# --不准许t_trigger.nc值为 tdsql_pgpostgres$# IF OLD.nc = 'tdsql_pg' THENpostgres$# RETURN NULL;postgres$# --RAISE EXCEPTION 'tdsql_pg不能被删除';postgres$# END IF;postgres$# RETURN OLD;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# CREATE TRIGGER t_trigger_delete_trigger BEFORE DELETE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_delete_trigger_func();CREATE TRIGGERpostgres=# INSERT INTO t_trigger VALUES(2,'tdsql_pg');INSERT 0 1postgres=# SELECT * t_triggpostgres=# SELECT * FROM t_trigger ;id | nc----+-------------------------1 | tdsql_pg_0.4260934544727212 | tdsql_pg(2 rows)postgres=# DELETE FROM t_trigger WHERE id=2;DELETE 0postgres=# SELECT * FROM t_trigger ;id | nc----+-------------------------1 | tdsql_pg_0.4260934544727212 | tdsql_pg(2 rows)
删除触发器
postgres=# drop TRIGGER t_trigger_insert_trigger on t_trigger;DROP TRIGGER
触发器使用限制
分区表,冷热分区表和复制表不支持使用触发器。