触发器函数
更新时间 2025-02-14 10:23:07
最近更新时间: 2025-02-14 10:23:07
本页介绍天翼云TeleDB数据库PL/pgsql的触发器函数。
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触发器使用限制
分区表，冷热分区表和复制表不支持使用触发器。
