学习目标
学习openGauss触发器
触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个触发事件（Event），如果该对象上拥有该事件对应的触发器，那么就会检查触发器的触发条件（Condition）是否满足，如果满足触发条件，那么就会执行触发动作（Action）
课程学习实操
连接数据库
#第一次进入等待15秒 #数据库启动中... 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);omm=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ; CREATE TABLE omm=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT); CREATE TABLE omm=# omm=#
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;omm=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS omm-# $$ omm$# DECLARE omm$# BEGIN omm$# INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3); omm$# RETURN NEW; omm$# omm$# END $$ LANGUAGE PLPGSQL; CREATE FUNCTION omm=#
–创建INSERT触发器
CREATE TRIGGER insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func();omm=# CREATE TRIGGER insert_trigger omm-# BEFORE INSERT ON test_trigger_src_tbl omm-# FOR EACH ROW omm-# EXECUTE PROCEDURE tri_insert_func(); CREATE TRIGGER omm=#
–执行INSERT触发事件并检查触发结果
INSERT INTO test_trigger_src_tbl VALUES(100,200,300);omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300); INSERT 0 1 omm=#
–查看触发操作生效
SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl;omm=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 300 (1 row) omm=# SELECT * FROM test_trigger_des_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 300 (1 row) omm=#
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;omm=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS omm-# $$ omm$# DECLARE omm$# BEGIN omm$# UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1; omm$# RETURN OLD; END omm$# $$ LANGUAGE PLPGSQL;omm$# omm=# CREATE FUNCTION
–创建update触发器
CREATE TRIGGER update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func();omm=# CREATE TRIGGER update_trigger omm-# FOR EACH ROW omm-# AFTER UPDATE ON test_trigger_src_tbl omm-# EXECUTE PROCEDURE tri_update_func(); CREATE TRIGGER omm=#
–执行update触发事件并检查触发结果
UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;omm=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100; UPDATE 1 omm=#
–查看触发操作生效
SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl;omm=# SELECT * FROM test_trigger_src_tbl; (1 row) omm=# id1 | id2 | id3 -----+-----+----- 100 | 200 | 400 SELECT * FROM test_trigger_des_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 400 (1 row) omm=#
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;omm=# omm$# DECLARE omm$# BEGIN omm$# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS omm-# $$ DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1; omm$# RETURN OLD; omm$# omm$# END $$ LANGUAGE PLPGSQL; CREATE FUNCTION omm=#
–创建delete触发器
CREATE TRIGGER delete_trigger BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func();omm=# CREATE TRIGGER delete_trigger omm-# BEFORE DELETE ON test_trigger_src_tbl omm-# FOR EACH ROW omm-# EXECUTE PROCEDURE tri_delete_func(); CREATE TRIGGER omm=#
–执行delete触发事件并检查触发结果
DELETE FROM test_trigger_src_tbl WHERE id1=100;omm=# DELETE FROM test_trigger_src_tbl WHERE id1=100; DELETE 1 omm=#
–查看触发操作生效
SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl;omm=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- (0 rows) omm=# SELECT * FROM test_trigger_des_tbl; id1 | id2 | id3 -----+-----+----- (0 rows) omm=#
5.修改触发器
–重命名
ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;omm=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed; ALTER TRIGGER omm=#
–在系统表PG_TRIGGER中查看触发器
select * from PG_TRIGGER;omm=# select * from PG_TRIGGER; tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgcon straint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner ---------+------------------------+--------+--------+-----------+--------------+---------------+---------------+------ --------+--------------+----------------+---------+--------+--------+--------+--------- 16398 | insert_trigger | 16404 | 7 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | 10 16398 | update_trigger | 16406 | 17 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | 10 16398 | delete_trigger_renamed | 16408 | 11 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | 10 (3 rows) omm=#
–查看表上的所有触发器
\dS+ test_trigger_src_tblomm=# \dS+ test_trigger_src_tbl Table "public.test_trigger_src_tbl" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- id1 | integer | | plain | | id2 | integer | | plain | | id3 | integer | | plain | | Triggers: delete_trigger_renamed BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func() insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func() update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func() Has OIDs: no Options: orientation=row, compression=no omm=#
6.禁用触发器
–禁用insert_trigger触发器
ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger; ALTER TABLE omm=#
–执行INSERT
INSERT INTO test_trigger_src_tbl VALUES(100,200,300);omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300); INSERT 0 1 omm=#
–查看触发操作没有生效
SELECT * FROM test_trigger_src_tbl; SELECT * FROM test_trigger_des_tbl;omm=# SELECT * FROM test_trigger_src_tbl; id1 | id2 | id3 -----+-----+----- 100 | 200 | 300 (1 row) omm=# SELECT * FROM test_trigger_des_tbl; id1 | id2 | id3 -----+-----+----- (0 rows) omm=#
–禁用当前表上所有触发器
ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL; ALTER TABLE omm=#
7.删除触发器
DROP TRIGGER update_trigger ON test_trigger_src_tbl; DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;omm=# DROP TRIGGER update_trigger ON test_trigger_src_tbl; DROP TRIGGER omm=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl; DROP TRIGGER omm=#
课程作业
1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
omm=# create table source_table (id int); CREATE TABLE omm=# create table target_table (id int); CREATE TABLE omm=#omm=# create or replace function tri_insert_func() returns trigger AS omm-# $$ omm$# DECLARE omm$# BEGIN omm$# insert into target_table values(new.id); omm$# return new; omm$# end omm$# $$ language plpgsql; CREATE FUNCTION omm=#omm=# create trigger insert_trigger omm-# before insert on source_table omm-# for each ROW omm-# execute PROCEDURE tri_insert_func(); CREATE TRIGGER omm=#
2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
omm=# insert into source_table values(1),(2),(3); INSERT 0 3 omm=# select * from source_table; id ---- 1 2 3 (3 rows) omm=# select * from target_table; id ---- 1 2 3 (3 rows) omm=#omm=# alter table source_table disable trigger insert_trigger; ALTER TABLE omm=# insert into source_table values (4),(5),(6); INSERT 0 3 omm=# omm=# select * from source_table; id ---- 1 2 3 4 5 6 (6 rows) omm=# select * from target_table; id ---- 1 2 3 (3 rows) omm=#
3.使用系统表PG_TRIGGER和\dS+查看触发器
omm=# select * from pg_trigger; tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner ---------+----------------+--------+--------+-----------+--------------+---------------+---------------+-------------- +--------------+----------------+---------+--------+--------+--------+--------- 16398 | insert_trigger | 16404 | 7 | D | f | 0 | 0 | 0 | f | f | 0 | | \x | | 10 16413 | insert_trigger | 16404 | 7 | D | f | 0 | 0 | 0 | f | f | 0 | | \x | | 10 (2 rows) omm=#omm=# \dS+ source_table Table "public.source_table" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- id | integer | | plain | | Disabled triggers: insert_trigger BEFORE INSERT ON source_table FOR EACH ROW EXECUTE PROCEDURE tri_insert_func() Has OIDs: no Options: orientation=row, compression=no omm=#
4.重命名触发器
omm=# ALTER TRIGGER insert_trigger ON source_table RENAME TO insert_trigger_new; ALTER TRIGGER omm=# \dS+ source_table Table "public.source_table" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- id | integer | | plain | | Disabled triggers: insert_trigger_new BEFORE INSERT ON source_table FOR EACH ROW EXECUTE PROCEDURE tri_insert_func() Has OIDs: no Options: orientation=row, compression=no omm=#
5.删除触发器
omm=# drop trigger insert_trigger_new on source_table; DROP TRIGGER omm=#
学习总结
通过本节课的学习，我掌握了触发器的基本操作，包括创建触发器函数，创建插入，更新和删除的触发器，重命名触发器，删除触发器。
最后修改时间：2021-12-18 11:55:52
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
