openGauss每日一练第18天|openGauss触发器

学习目标
学习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=#
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$# omm$# BEGIN
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# RETURN NEW;
omm$# END
omm$# $$ 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
–查看触发操作生效

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)

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;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE

–创建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-# AFTER UPDATE ON test_trigger_src_tbl
omm-# FOR EACH ROW
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
omm=# SELECT * FROM test_trigger_des_tbl;
id1 | id2 | id3
-----±----±----
100 | 200 | 400
(1 row)

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=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# omm$# BEGIN
RETURN OLD;
omm$# DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
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();

–执行delete触发事件并检查触发结果

DELETE FROM test_trigger_src_tbl WHERE id1=100;

omm=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
DELETE 1

–查看触发操作生效

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
-----±----±----
100 | 200 | 400
(1 row)
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;
ERROR: trigger “delete_trigger” for table “test_trigger_src_tbl” does not exist

–在系统表PG_TRIGGER中查看触发器

select * from PG_TRIGGER;

omm=# select * from PG_TRIGGER;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | 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
(2 rows)

–查看表上的所有触发器

\dS+ test_trigger_src_tbl

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:
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

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
–查看触发操作没有生效

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 | 400
(1 row)

–禁用当前表上所有触发器

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;

omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
ALTER TABLE

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
课程作业

1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
create table tab1(id1 int,id2 int,id3 int);
create table tab2(id1 int,id2 int,id3 int);
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO tab1 VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER insert_trigger
BEFORE INSERT ON tab2
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();

omm=# create table tab1(id1 int,id2 int,id3 int);
CREATE TABLE
omm=# create table tab2(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 tab1 VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# RETURN NEW;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# CREATE TRIGGER insert_trigger
omm-# BEFORE INSERT ON tab2
FOR EACH ROW
omm-# omm-# EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGG

2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
INSERT INTO tab2 VALUES(1000,2000,3000);
select * from tab1;
select * from tab2;
alter table tab2 disable trigger insert_trigger;
INSERT INTO tab2 VALUES(4000,5000,6000);
select * from tab1;
select * from tab2;

omm=# INSERT INTO tab2 VALUES(1000,2000,3000);
INSERT 0 1
omm=# select * from tab1;
id1 | id2 | id3
------±-----±-----
1000 | 2000 | 3000
(1 row)

omm=# select * from tab2;
id1 | id2 | id3
------±-----±-----
1000 | 2000 | 3000
(1 row)

omm=# alter table tab2 disable trigger insert_trigger;
ALTER TABLE
omm=# INSERT INTO tab2 VALUES(4000,5000,6000);
INSERT 0 1
omm=# select * from tab1;

(1 row)

id1 | id2 | id3
------±-----±-----
1000 | 2000 | 3000
4000 | 5000 | 6000
(2 rows)
omm=# select * from tab2;
id1 | id2 | id3
------±-----±-----
1000 | 2000 | 3000
omm=#

3.使用系统表PG_TRIGGER和\dS+查看触发器
select * from pg_trigger;

omm=# select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr
| tgargs | tgqual | tgowner
---------±---------------±-------±-------±----------±-------------±--------------±--------------±-------------±-------------±---------------±--------±------
-±-------±-------±--------
16392 | insert_trigger | 16395 | 7 | D | f | 0 | 0 | 0 | f | f | 0 |
| \x | | 10
(1 row)

omm=#

\dS+ tab2

omm=# \dS+ tab2
Table “public.tab2”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------±----------±--------±-------------±------------
id1 | integer | | plain | |
id2 | integer | | plain | |
id3 | integer | | plain | |
Disabled triggers:
insert_trigger BEFORE INSERT ON tab2 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no

4.重命名触发器
alter trigger insert_trigger on tab2 rename to insert_trigger_renamed;

omm=# alter trigger insert_trigger on tab2 rename to insert_trigger_renamed;
ALTER TRIGGER

5.删除触发器
drop trigger insert_trigger_renamed on tab2;

omm=# drop trigger insert_trigger_renamed on tab2;
DROP TRIGGER