openGauss每日一练第18天 加油

学习笔记

insert/update/delete触发函数
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; 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; 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; insert/update/delete触发器 CREATE TRIGGER insert_trigger BEFORE INSERT ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_insert_func(); CREATE TRIGGER update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func(); CREATE TRIGGER delete_trigger BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func();   ### 课后作业 1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数 omm=# create table src_tb1(id1 int,id2 int,id3 int); CREATE TABLE omm=# create table des_tb1(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 des_tb1 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 src_tb1
omm-# for each row
omm-# execute procedure tri_insert_func();
CREATE TRIGGER

omm=# \dS+ src_tb1;
Table “public.src_tb1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------±----------±--------±-------------±------------
id1 | integer | | plain | |
id2 | integer | | plain | |
id3 | integer | | plain | |
Triggers:
insert_trigger BEFORE INSERT ON src_tb1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效

omm=# insert into src_tb1 values(100,101,102),(200,201,202);
INSERT 0 2
omm=# select * from src_tb1;
id1 | id2 | id3
-----±----±----
100 | 101 | 102
200 | 201 | 202
(2 rows)

omm=# select * from des_tb1;
id1 | id2 | id3
-----±----±----
100 | 101 | 102
200 | 201 | 202
(2 rows)

omm=# alter table src_tb1 disable trigger insert_trigger;
ALTER TABLE

omm=# insert into src_tb1 values(300,301,302);
INSERT 0 1

omm=# select * from src_tb1;
id1 | id2 | id3
-----±----±----
100 | 101 | 102
200 | 201 | 202
300 | 301 | 302
(3 rows)

omm=# select * from des_tb1;
id1 | id2 | id3
-----±----±----
100 | 101 | 102
200 | 201 | 202
(2 rows)
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
---------±---------------±-------±-------±----------±-------------±--------------±--------------±-------------±-------------±---------------±--------±-------±-------±-------±--------
16401 | insert_trigger | 16395 | 7 | D | f | 0 | 0 | 0 | f | f | 0 | | \x | | 10
(1 row)

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

4.重命名触发器

omm=# alter trigger insert_trigger on src_tb1 rename to insert_trigger_new;
ALTER TRIGGER

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

5.删除触发器

omm=# drop trigger insert_trigger_new on src_tb1;
DROP TRIGGER

omm=# \dS+ src_tb1;
Table “public.src_tb1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------±----------±--------±-------------±------------
id1 | integer | | plain | |
id2 | integer | | plain | |
id3 | integer | | plain | |
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from PG_TRIGGER;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------±-------±-------±-------±----------±-------------±--------------±--------------±-------------±-------------±---------------±--------±-------±-------±-------±--------
(0 rows)