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

openGauss每日一练第18天课后作业

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

create table lzm_tri_src(id int,first_name char(20),last_name char(20));

create table lzm_tri_des(id int,first_name char(20),last_name char(20));

CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO lzm_tri_des VALUES(NEW.id, NEW.first_name, NEW.last_name);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER lzm_insert_trigger
BEFORE INSERT ON lzm_tri_src
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();

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

insert into lzm_tri_src values (1,'Harry','Potter');

select * from lzm_tri_des;

--禁用触发器后，再往lzm_tri_src插入数据

alter table lzm_tri_src disable trigger lzm_insert_trigger;

insert into lzm_tri_src values (2,'Wendy','Baker');

select * from lzm_tri_des;

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

select * from pg_trigger;

\dS+  lzm_tri_src;

4.重命名触发器

alter trigger lzm_insert_trigger on lzm_tri_src rename to lzm_insert_tri_renamed;

5.删除触发器

drop trigger lzm_insert_tri_renamed on lzm_tri_src;