openGauss每日一练第18天 | 触发器的相关操作

今日的学习目标：
学习openGauss触发器

今日的学习体会：
触发器，可以在表的部分DML操作时，被自动触发。

今日的学习步骤：
课程学习
连接数据库
su - omm
gsql -r
1.创建源表及触发表
CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

2.insert触发器
–创建触发器函数

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; –创建INSERT触发器 CREATE TRIGGER insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func(); –执行INSERT触发事件并检查触发结果 INSERT INTO test_trigger_src_tbl VALUES(100,200,300); –查看触发操作生效 SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl; 3.update触发器 –创建触发器函数 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; –创建update触发器 CREATE TRIGGER update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func(); –执行update触发事件并检查触发结果 UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100; –查看触发操作生效 SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl; 4.delete触发器 –创建触发器函数 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; –创建delete触发器 CREATE TRIGGER delete_trigger BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func(); –执行delete触发事件并检查触发结果 DELETE FROM test_trigger_src_tbl WHERE id1=100; –查看触发操作生效 SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl; 5.修改触发器 –重命名 ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed; –在系统表PG_TRIGGER中查看触发器 select * from PG_TRIGGER; –查看表上的所有触发器 \dS+ test_trigger_src_tbl 6.禁用触发器 –禁用insert_trigger触发器 ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger; –执行INSERT INSERT INTO test_trigger_src_tbl VALUES(100,200,300); –查看触发操作没有生效 SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl; –禁用当前表上所有触发器 ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL; 7.删除触发器 DROP TRIGGER update_trigger ON test_trigger_src_tbl; DROP TRIGGER delete_trigger_rena