openGauss插件使用指南:SQL语法—CREATE TRIGGER（2）

最新推荐文章于 2024-09-05 09:00:00 发布
openGauss小助手
最新推荐文章于 2024-09-05 09:00:00 发布
阅读量235
点赞数 4
文章标签： sql 前端数据库 openGauss
本文链接：https://blog.csdn.net/weixin_53596073/article/details/140290710
版权
示例

--创建源表及触发表
openGauss=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
openGauss=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

--创建触发器函数
openGauss=# 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;

openGauss=# 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;

openGauss=# 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触发器
openGauss=# CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_func();

--创建UPDATE触发器
openGauss=# CREATE TRIGGER update_trigger
           AFTER UPDATE ON test_trigger_src_tbl  
           FOR EACH ROW
           EXECUTE PROCEDURE tri_update_func();

--创建DELETE触发器
openGauss=# CREATE TRIGGER delete_trigger
           BEFORE DELETE ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_delete_func();

--执行INSERT触发事件并检查触发结果
openGauss=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效。

--执行UPDATE触发事件并检查触发结果
openGauss=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效

--执行DELETE触发事件并检查触发结果
openGauss=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效

--修改触发器
openGauss=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

--禁用insert_trigger触发器
openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;  

--禁用当前表上所有触发器
openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;  

--删除触发器
openGauss=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
openGauss=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
openGauss=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;
--创建B兼容性数据库
openGauss=# create database db_mysql dbcompatibility 'B';
--创建触发器定义用户
openGauss=# create user test_user password 'Gauss@123';
--创建原表及触发表
db_mysql=# create table test_mysql_trigger_src_tbl (id INT);
db_mysql=# create table test_mysql_trigger_des_tbl (id INT);
db_mysql=# create table animals (id INT, name CHAR(30));
db_mysql=# create table food (id INT, foodtype VARCHAR(32), remark VARCHAR(32), time_flag TIMESTAMP);
--创建MySQL兼容definer语法触发器
db_mysql=# create definer=test_user trigger trigger1
					after insert on test_mysql_trigger_src_tbl
					for each row
					begin 
    				 insert into test_mysql_trigger_des_tbl values(1);
					end;
					/
--创建MySQL兼容trigger_order语法触发器
db_mysql=# create trigger animal_trigger1
 					after insert on animals
					for each row
					begin
    				 insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
					end;
					/
--创建MySQL兼容FOLLOWS触发器
db_mysql=# create trigger animal_trigger2
					after insert on animals
					for each row
					follows animal_trigger1
					begin
    				 insert into food(id, foodtype, remark, time_flag) values (2,'chocolate', 'sdsdsdsd', now());
					end;
					/
db_mysql=# create trigger animal_trigger3
          after insert on animals
          for each row
          follows animal_trigger1
          begin
              insert into food(id, foodtype, remark, time_flag) values (3,'cake', 'sdsdsdsd', now());
          end;
          /
db_mysql=# create trigger animal_trigger4
          after insert on animals
          for each row
          follows animal_trigger1
          begin
              insert into food(id, foodtype, remark, time_flag) values (4,'sausage', 'sdsdsdsd', now());
          end;
          /
--执行insert触发事件并检查触发结果
db_mysql=# insert into animals (id, name) values(1,'lion');
db_mysql=# select * from animals;
db_mysql=# select id, foodtype, remark from food;
--创建MySQL兼容PROCEDES触发器
db_mysql=# create trigger animal_trigger5
          after insert on animals
          for each row
          precedes animal_trigger3
          begin
              insert into food(id, foodtype, remark, time_flag) values (5,'milk', 'sdsds', now());
          end;
          /
db_mysql=# create trigger animal_trigger6
          after insert on animals
          for each row
          precedes animal_trigger2
          begin
              insert into food(id, foodtype, remark, time_flag) values (6,'strawberry', 'sdsds', now());
          end;
          /
--执行insert触发事件并检查触发结果
db_mysql=# insert into animals (id, name) values(2, 'dog');
db_mysql=# select * from animals;
db_mysql=# select id, foodtype, remark from food;
--创建MySql
--创建MySQL兼容if not exists语法触发器
db_mysql=# create trigger if not exists animal_trigger1
          after insert on animals
          for each row
          begin
              insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
          end;
          /
--创建MySQL格式触发器
db_mysql=# delimiter //

db_mysql=# create trigger animal_d_trigger1
          after insert on animals
          for each row
          begin
              insert into food (id ,foodtype, remark, time_flag) values(1,'ice','avcs', now());
          end;
          //

db_mysql=# delimiter ;
--创建MySQL兼容trigger_body为单条sql语法触发器
db_mysql=# create trigger animal_trigger_single
          after insert on animals
          for each row
          insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());

--在指定模式下创建、重命名、删除触发器语法，触发器的模式需要与表模式相同
create schema testscm;
create table food (id int, foodtype varchar(32), remark varchar(32), time_flag timestamp);
create table testscm.animals_scm (id int, name char(30));
-- 在指定模式下创建触发器
create trigger testscm.animals_trigger
after insert on testscm.animals_scm
for each row
begin
    insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now());
end;
/
create trigger if not exists testscm.animals_trigger
after insert on testscm.animals_scm
for each row
begin
    insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now());
end;
/
-- 重命名指定模式下的触发器
-- 由于重命名触发器不支持修改触发器所属模式，因此新触发器名不支持携带模式名
alter trigger testscm.animals_trigger on testscm.animals_scm rename to animals_trigger_new;
-- 删除指定模式下的触发器
drop trigger testscm.animals_trigger_new;
drop trigger if exists testscm.animals_trigger_new;
-- 当删除触发器不指定所在表时，若存在多个同名触发器，不支持通过指定触发器的模式名来确定所在表以及触发器，如下示例：
testdb_m=# create schema testscm;
CREATE SCHEMA
testdb_m=# create table food (id int, foodtype varchar(32), remark varchar(32), time_flag timestamp);
CREATE TABLE
testdb_m=# create table animals (id int, name char(30));
CREATE TABLE
testdb_m=# create table testscm.animals_scm (id int, name char(30));
CREATE TABLE
testdb_m=# create or replace function food_function() returns trigger as
testdb_m-# $$
testdb_m$# declare
testdb_m$# begin
testdb_m$#     insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now());
testdb_m$# return new;
testdb_m$# end
testdb_m$# $$ language plpgsql;
CREATE FUNCTION
testdb_m=# create trigger animals_trigger before insert on animals for each row execute procedure food_function();
CREATE TRIGGER
testdb_m=# create trigger testscm.animals_trigger before insert on testscm.animals_scm for each row execute procedure food_function();
CREATE TRIGGER
testdb_m=# drop TRIGGER testscm.animals_trigger;
ERROR:  trigger named "animals_trigger" has more than one trigger, please use drop trigger on syntax