云数据库 GAUSSDB-CREATE TRIGGER:示例

时间：2024-04-26 16:14:16

云数据库 GAUSSDB

示例

--创建源表及触发表
gaussdb=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
gaussdb=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

--创建触发器函数
gaussdb=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
                   RETURN NEW;
           END
           $$ LANGUAGE plpgsql;

gaussdb=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE plpgsql;

gaussdb=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE plpgsql;

--创建INSERT触发器
gaussdb=# CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_func();

--创建UPDATE触发器
gaussdb=# CREATE TRIGGER update_trigger
           AFTER UPDATE ON test_trigger_src_tbl  
           FOR EACH ROW
           EXECUTE PROCEDURE tri_update_func();

--创建DELETE触发器
gaussdb=# CREATE TRIGGER delete_trigger
           BEFORE DELETE ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_delete_func();

--执行INSERT触发事件并检查触发结果
gaussdb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
gaussdb=# SELECT * FROM test_trigger_src_tbl;
gaussdb=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效。

--执行UPDATE触发事件并检查触发结果
gaussdb=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
gaussdb=# SELECT * FROM test_trigger_src_tbl;
gaussdb=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效

--执行DELETE触发事件并检查触发结果
gaussdb=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
gaussdb=# SELECT * FROM test_trigger_src_tbl;
gaussdb=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效

--修改触发器
gaussdb=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

--禁用insert_trigger触发器
gaussdb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;  

--禁用当前表上所有触发器
gaussdb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;  

--删除触发器
gaussdb=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
gaussdb=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
gaussdb=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;