openGauss每日一练第18天|触发器的使用练习

触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个触发事件（Event），如果该对象上拥有该事件对应的触发器，那么就会检查触发器的触发条件（Condition）是否满足，如果满足触发条件，那么就会执行触发动作（Action）

其实触发器就很像是一个可以自由灵活添加回调函数的一个特性，这个特性可以与其他的数据表结合在一起，在这里例子里面就可以同步数据的添加删除更改等，定义非常方便，使用也很直观。

课程学习

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);

–查看触发操作生效

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;

–创建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;

–查看触发操作生效

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)

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;

–查看触发操作生效

omm=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

omm=# SELECT * FROM test_trigger_des_tbl;  
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

5.修改触发器

–重命名

ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

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

select * from PG_TRIGGER;

 tgrelid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgc
onstraint | 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
   16389 | delete_trigger_renamed |  16399 |     11 | O         | f            |             0 |             0 |    
        0 | f            | f              |       0 |        | \x     |        |      10
(3 rows)

–查看表上的所有触发器

\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

6.禁用触发器

–禁用insert_trigger触发器

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;

–执行INSERT

INSERT INTO test_trigger_src_tbl VALUES(100,200,300);

–查看触发操作没有生效

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;

7.删除触发器

DROP TRIGGER update_trigger ON test_trigger_src_tbl;
DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;      

课程作业

1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数

CREATE TABLE trigger_table_src(id1 INT, id2 INT, id3 INT);
CREATE TABLE trigger_table_des(id1 INT, id2 INT, id3 INT);

create or replace function tri_insert_func() returns trigger as
$$
declare begin
insert into trigger_table_des values(new.id1, new.id2, new.id3);
return new;
end
$$ language plpgsql;

create trigger insert_trigger before insert on trigger_table_src
for each row
execute procedure tri_insert_func();

2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效

insert into trigger_table_src values (300, 200, 100);

omm=# select * from trigger_table_src;
 id1 | id2 | id3 
-----+-----+-----
 300 | 200 | 100
(1 row)

omm=# select * from trigger_table_des;
 id1 | id2 | id3 
-----+-----+-----
 300 | 200 | 100
(1 row)

alter table trigger_table_src disable trigger insert_trigger;
insert into trigger_table_src values (600, 500, 400);

omm=# select * from trigger_table_src;
 id1 | id2 | id3 
-----+-----+-----
 300 | 200 | 100
 600 | 500 | 400
(2 rows)


omm=# select * from trigger_table_des;
 id1 | id2 | id3 
-----+-----+-----
 300 | 200 | 100
(1 row)

3.使用系统表PG_TRIGGER和\dS+查看触发器

select * from PG_TRIGGER;

 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstrain
t | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+------------
--+--------------+----------------+---------+--------+--------+--------+---------
   16389 | insert_trigger |  16395 |      7 | D         | f            |             0 |             0 |            
0 | f            | f              |       0 |        | \x     |        |      10
   16401 | insert_trigger |  16395 |      7 | D         | f            |             0 |             0 |            
0 | f            | f              |       0 |        | \x     |        |      10
(2 rows)

\dS+ test_trigger_src_tbl

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

4.重命名触发器

alter trigger insert_trigger on trigger_table_src rename to insert_trigger1;

5.删除触发器

alter table trigger_table_src disable trigger all;
drop trigger insert_trigger1 on trigger_table_src;

drop trigger的时候一定要指明这个trigger是作用在哪个数据表上面的，不然会报错。