觸發器函數
更新時間 2025-02-14 10:24:39
最近更新時間: 2025-02-14 10:24:39
分享文章
本頁介紹天翼云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觸發器使用限制
分區表，冷熱分區表和復制表不支持使用觸發器。
