openGauss每日一练第18天 | 触发器 from seven
学习目标
学习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);
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_renamed ON test_trigger_src_tbl;
课程作业
1. 创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
omm=# create table t1 (id int,name char(30));
CREATE TABLE
omm=# create table t2(id int,name char(30));
CREATE TABLE
omm=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+----------------------+-------+-------+----------------------------------
public | t1 | table | omm | {orientation=row,compression=no}
public | t2 | table | omm | {orientation=row,compression=no}
public | test_trigger_des_tbl | table | omm | {orientation=row,compression=no}
public | test_trigger_src_tbl | table | omm | {orientation=row,compression=no}
(4 rows)
omm=# create or replace function tri_insert_t1() returns trigger
omm-# as $$
omm$# declare
omm$# begin
omm$# insert into t2 values(new.id,new.name);
omm$# return new;
omm$# end
omm$# $$ language plpgsql;
CREATE FUNCTION
omm=# create trigger ins_triomm-#
before insert on t1
omm-# for each row
omm-# execute procedure tri_insert_t1();
CREATE TRIGGER
2. 在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
omm=# insert into t1 values(101,'mary');
INSERT 0 1
omm=# select * from t2;
id | name
-----+--------------------------------
101 | mary
(1 row)
omm=# alter table t1 disable trigger ins_tri;
ALTER TABLE
omm=# insert into t1 values(102,'tom');
INSERT 0 1
omm=# select * from t2;
id | name
-----+--------------------------------
101 | mary
(1 row)
omm=# select * from t1;
id | name
-----+--------------------------------
101 | mary
102 | tom
(2 rows)
3. 使用系统表PG_TRIGGER和\dS+查看触发器
omm=# select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdef
erred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------
------+---------+--------+--------+--------+---------
16389 | insert_trigger | 16395 | 7 | O | f | 0 | 0 | 0 | f | f
| 0 | | \x | | 10
16401 | ins_tri | 16407 | 7 | D | f | 0 | 0 | 0 | f | f
| 0 | | \x | | 10
(2 rows)
omm=# \dS+ t1
Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Disabled triggers:
ins_tri BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_t1()
Has OIDs: no
Options: orientation=row, compression=no
4. 重命名触发器
omm=# alter trigger ins_tri on t1 rename to ins_tri_t1;
ALTER TRIGGER
omm=# select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdef
erred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------
------+---------+--------+--------+--------+---------
16389 | insert_trigger | 16395 | 7 | O | f | 0 | 0 | 0 | f | f
| 0 | | \x | | 10
16401 | ins_tri_t1 | 16407 | 7 | D | f | 0 | 0 | 0 | f | f
| 0 | | \x | | 10
(2 rows)
5. 删除触发器
omm=# drop trigger insert_trigger on test_trigger_src_tbl;
omm=# DROP TRIGGER
omm=# drop table t1;
DROP TABLE
