CREATE TRIGGER

CreateTrigger ::= CREATE [ CONSTRAINT ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR '...'] }
    ON table_name
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments );

CreateTrigger ::= CREATE [ CONSTRAINT ] [ DEFINER = user] TRIGGER [ IF NOT EXISTS ] trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR '...' ] }
    ON table_name
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    [ trigger_order ]
    trigger_body

   INSERT
   UPDATE [ OF column_name [, ... ] ]
   DELETE
   TRUNCATE

 trigger_order ::=  { FOLLOWS|PRECEDES } other_trigger_name

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

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

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

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

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

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

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

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

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

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

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

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

--删除触发器
MogDB=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
MogDB=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
MogDB=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;

--创建mysql兼容数据库
MogDB=# create database db_mysql dbcompatibility 'B';
--创建触发器定义用户
MogDB=# create user test_user password 'Enmo@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兼容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=# drop trigger animal_trigger1;
db_mysql=# drop trigger if exists animal_trigger1;
--在指定模式下创建、重命名、删除触发器语法，触发器的模式需要与表模式相同
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;