学习openGauss触发器
背景：触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个触发事件（Event），如果该对象上拥有该事件对应的触发器，那么就会检查触发器的触发条件（Condition）是否满足，如果满足触发条件，那么就会执行触发动作（Action）
1.登录数据库环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
2.创建数据库表
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
3.创建数据库函数
omm$# DECLARE
omm$# BEGIN
omm$# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# RETURN NEW;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
4.创建插入触发器
omm=# CREATE TRIGGER insert_trigger
omm-# FOR EACH ROW
omm-# BEFORE INSERT ON test_trigger_src_tbl
omm-# EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGGER
5.插入测试数据以验证触发器
omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
查看源表
omm=# SELECT * FROM test_trigger_src_tbl;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 300
(1 row)
查看目标表
omm=# SELECT * FROM test_trigger_des_tbl;
omm=# id1 | id2 | id3
-----+-----+-----
100 | 200 | 300
(1 row)
===》触发器起作用了！！！
6.创建update 触发器
函数
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;
omm$# omm$# END
$$ LANGUAGE PLPGSQL;
CREATE FUNCTION
触发器
omm=# CREATE TRIGGER update_trigger
omm-# AFTER UPDATE ON test_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_update_func();
CREATE TRIGGER
模拟update操作
omm=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
UPDATE 1
查看源表
omm=# SELECT * FROM test_trigger_src_tbl;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 400
(1 row)
查看目标表
omm=# SELECT * FROM test_trigger_des_tbl;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 400
(1 row)
6.创建delete触发器
delete触发器函数
omm=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
omm$# RETURN OLD;
omm$# omm$# END
$$ LANGUAGE PLPGSQL;
CREATE FUNCTION
delete触发器
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操作
omm=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
DELETE 1
omm=# SELECT * FROM test_trigger_src_tbl;
id1 | id2 | id3
-----+-----+-----
(0 rows)
omm=# SELECT * FROM test_trigger_des_tbl;
id1 | id2 | id3
-----+-----+-----
(0 rows)
7.重命名触发器
omm=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
ALTER TRIGGER
8.查看触发器函数
omm=# select * from PG_TRIGGER;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgde
ferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+-----
---------+----------------+---------+--------+--------+--------+---------
16389 | insert_trigger | 16395 | 7 | O | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
16389 | update_trigger | 16397 | 17 | O | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
16389 | delete_trigger_renamed | 16399 | 11 | O | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
(3 rows)
9.查看触发器
omm=# \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
10.禁用触发器
omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
ALTER TABLE
模拟插入数据
omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
查看源表
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)
禁用表上的触发器
ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
ALTER TABLE
删除触发器
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
作业
1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
omm=# CREATE TABLE alex_source(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE
omm=# CREATE TABLE alex_target(id1 INT, id2 INT, id3 INT);
CREATE TABLE
omm=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# INSERT INTO alex_target VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# RETURN NEW;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# CREATE TRIGGER insert_trigger_source
omm-# BEFORE INSERT ON alex_source
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGGER
2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
omm=# INSERT INTO alex_source VALUES(100,200,300);
INSERT 0 1
omm=# SELECT * FROM alex_source;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 300
(1 row)
omm=# SELECT * FROM alex_target;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 300
(1 row)
omm=# ALTER TABLE alex_source DISABLE TRIGGER insert_trigger_source;
ALTER TABLE
omm=# INSERT INTO alex_source VALUES(400,500,600);
INSERT 0 1
omm=# SELECT * FROM alex_source;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 300
400 | 500 | 600
(2 rows)
omm=# SELECT * FROM alex_target;
id1 | id2 | id3
-----+-----+-----
100 | 200 | 300
(1 row)
3.使用系统表PG_TRIGGER和\dS+查看触发器
omm=# select * from PG_TRIGGER;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdef
errable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+-----------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+------
--------+----------------+---------+--------+--------+--------+---------
16389 | insert_trigger | 16395 | 7 | D | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
16401 | insert_trigger_source | 16395 | 7 | D | f | 0 | 0 | 0 | f
| f | 0 | | \x | | 10
(2 rows)
omm=# \dS+ alex_source
Table "public.alex_source"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id1 | integer | | plain | |
id2 | integer | | plain | |
id3 | integer | | plain | |
Disabled triggers:
insert_trigger_source BEFORE INSERT ON alex_source FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
4.重命名触发器
omm=# ALTER TRIGGER insert_trigger_source ON alex_source RENAME TO insert_trigger_source_renamed;
ALTER TRIGGER
5.删除触发器
omm=# DROP TRIGGER insert_trigger_source_renamed ON alex_source;
DROP TRIGGER
