GAUSSDB-触发器

语法格式

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

其中event包含以下几种：

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

参数详细说明

CONSTRAINT
可选项，指定此参数将创建约束触发器，即触发器作为约束来使用。除了可以使用SET CONSTRAINTS调整触发器触发的时间之外，这与常规触发器相同。 约束触发器必须是AFTER ROW触发器。

trigger_name
触发器名称，该名称不能限定模式，因为触发器自动继承其所在表的模式，且同一个表的触发器不能重名。 对于约束触发器，使用SET CONSTRAINTS修改触发器行为时也使用此名称。
取值范围：符合标识符命名规范的字符串，且最大长度不超过63个字符。

BEFORE
触发器函数是在触发事件发生前执行。

AFTER
触发器函数是在触发事件发生后执行，约束触发器只能指定为AFTER。

INSTEAD OF
触发器函数直接替代触发事件。

event
启动触发器的事件，取值范围包括：INSERT、UPDATE、DELETE或TRUNCATE，也可以通过OR同时指定多个触发事件。

对于UPDATE事件类型，可以使用下面语法指定列：
UPDATE OF column_name1 [, column_name2 ... ]

表示只有这些列作为UPDATE语句的目标列时，才会启动触发器，但是INSTEAD OF UPDATE类型不支持指定列信息。

table_name
需要创建触发器的表名称。
取值范围：数据库中已经存在的表名称。

referenced_table_name
约束引用的另一个表的名称。 只能为约束触发器指定，常见于外键约束。由于当前不支持外键，因此不建议使用。

取值范围：数据库中已经存在的表名称。

DEFERRABLE | NOT DEFERRABLE
约束触发器的启动时机，仅作用于约束触发器。这两个关键字设置该约束是否可推迟。

详细介绍请参见CREATE TABLE。

INITIALLY IMMEDIATE | INITIALLY DEFERRED
如果约束是可推迟的，则这个子句声明检查约束的缺省时间，仅作用于约束触发器。

详细介绍请参见CREATE TABLE。

FOR EACH ROW | FOR EACH STATEMENT
触发器的触发频率。

FOR EACH ROW是指该触发器是受触发事件影响的每一行触发一次。
FOR EACH STATEMENT是指该触发器是每个SQL语句只触发一次。
未指定时默认值为FOR EACH STATEMENT。约束触发器只能指定为FOR EACH ROW。

condition
决定是否实际执行触发器函数的条件表达式。当指定WHEN时，只有在条件返回true时才会调用该函数。

在FOR EACH ROW触发器中，WHEN条件可以通过分别写入OLD.column_name或NEW.column_name来引用旧行或新行值的列。 需要注意，INSERT触发器不能引用OLD和DELETE触发器不能引用NEW。

INSTEAD OF触发器不支持WHEN条件。

WHEN表达式不能包含子查询。

对于约束触发器，WHEN条件的评估不会延迟，而是在执行更新操作后立即发生。 如果条件返回值不为true，则触发器不会排队等待延迟执行。

function_name
用户定义的函数，必须声明为不带参数并返回类型为触发器，在触发器触发时执行。

arguments
执行触发器时要提供给函数的可选的以逗号分隔的参数列表。参数是文字字符串常量，简单的名称和数字常量也可以写在这里，但它们都将被转换为字符串。 请检查触发器函数的实现语言的描述，以了解如何在函数内访问这些参数。

触发器种类

(1)、INSTEAD OF的触发器必须标记为FOR EACH ROW，并且只能在视图上定义。
(2)、BEFORE和AFTER触发器作用在视图上时，只能标记为FOR EACH STATEMENT。
(3)、TRUNCATE类型触发器仅限FOR EACH STATEMENT。

注意事项

(1)、当前仅支持在普通行存表上创建触发器，不支持在列存表、临时表、unlogged表等类型表上创建触发器。
(2)、如果为同一事件定义了多个相同类型的触发器，则按触发器的名称字母顺序触发它们。
(3)、一个触发器只能作用在一张表上，对创建的触发器数量无限制但一个表上的触发器越多，性能消耗越大。
(4)、触发器常用于多表间数据关联同步场景，对SQL执行性能影响较大，不建议在大数据量同步及对性能要求高的场景中使用。
(5)、当触发器满足如下条件时，触发语句能和触发器一起下推到DN执行并提升触发器执行性能：
(5.1)、开关enable_trigger_shipping和enable_fast_query_shipping开启（默认均开启）。
(5.2)、源表触发器使用的触发器函数为plpgsql类型（推荐类型）。
(5.3)、源表与触发表分布键的类型、数量完全相同，均为行存表，且所属的nodegroup相同。
(5.4)、原INSERT/UPDATE/DELETE语句条件中包含所有分布键与NEW/OLD等值比较表达式。
(5.5)、原INSERT/UPDATE/DELETE语句在没有触发器的情况下原本就能query shipping。
(5.6)、源表上只有INSERT/UPDATE/DELETE AFTER/BEFORE FOR EACH ROW六类触发器，且所有触发器都可下推。

示例

--删除表
DROP TABLE IF EXISTS gaussdb.student CASCADE ;
DROP TABLE IF EXISTS gaussdb.course CASCADE ;
DROP TABLE IF EXISTS gaussdb.elective CASCADE ;
--学生表
create table student(sno int,sname varchar(50));
--课程表
create table course(cno int,cname varchar(50));
--成绩表
create table elective(sno int,cno int,grade int);

insert into student values(1,'aaa');
insert into student values(2,'bbb');

insert into course values(1,'match');
insert into course values(2,'chinese');
insert into course values(3,'english');

insert into elective values(1,1,10);
insert into elective values(1,2,10);
insert into elective values(1,3,10);
insert into elective values(2,1,20);
insert into elective values(2,2,20);
insert into elective values(2,3,20);


--创建触发器DELETE_ELE，在STUDENT表上绑定触发器DELETE_ELE，在删除表中某个学生时，将ELECTIVE表中该学生的选课记录一并删除。

(1)、删除elective表记录的函数
create or replace function func_delete_ele() returns trigger as 
$$
begin
	delete from elective where sno=old.sno;
	return old;
end;
$$ language plpgsql;

(2)、绑定到student表的触发器
create trigger delete_ele 
before delete on student 
for each row 
execute PROCEDURE func_delete_ele();


--删除表
DROP TABLE IF EXISTS gaussdb.teacher CASCADE ;
DROP TABLE IF EXISTS gaussdb.department CASCADE ;
--创建表
create table teacher(id integer primary key,
name varchar(50) not null,
deptnd integer not null,
tetle varchar(50) not null);

create table department(id integer primary key,
name varchar(50) not null,
number_of_senior integer default 0);
--导入数据
insert into department values
(1,'physics',0),
(2,'mathmetics',0),
(3,'chemistry',0);

insert into teacher values
(1,'tom',1,'associateprofessor'),
(2,'bill',1,'professor'),
(11,'eiston','3','associate professor');


创建Tri_update_D触发器，如果修改Number_of_senior字段时提示“不能随便修改部门教授职称人数”。如果已经有了Tri_update_D触发器，则删除后再重建。

(1)、创建提示函数
create or replace function print_info() returns trigger 
as 
$$
begin
	raise EXCEPTION '不能随便修改部门教授职称人数';
	return null;
end;
$$ language plpgsql;

(2)、当触发器存在时将触发器删除，然后创建触发器
drop trigger if exists Tri_update_D on department;

(3)、绑定到department表的触发器
create trigger Tri_update_D 
Before update of number_of_senior on department 
for each row 
execute procedure print_info();

(4)、禁用 触发器

alter table department disable trigger Tri_update_D;


--删除表
DROP TABLE IF EXISTS gaussdb.tab1 ;
DROP TABLE IF EXISTS gaussdb.dept ;
DROP TABLE IF EXISTS gaussdb.logger ;
--创建表并插入数据
create table tab1(sname text, deptno int, salary float, title text);  
create table dept(id int, dept_name text);
create table logger(sname text, dept_name text, log_date date);
insert into dept values(1,'dept1'),(2,'dept2');

创建触发器，要求在tab1表插入一行数据时，自动往logger表中插入一条记录，记录sname和部门名称, 并用当天的日期来标记该行数据的生成时间。

--1、创建函数T_INS_F
【方法1】
create or replace function T_INS_F() 
returns trigger as
$$
begin
insert into logger values(new.sname,
(select dept_name from dept where id=new.deptno),sysdate
);
return new;
end;
$$ language plpgsql;

【方法2】
create or replace function T_INS_F() returns trigger as 
$$
declare v_dept_name text;
begin
	v_dept_name := (select dept_name from dept where id=NEW.deptno);
	insert into logger values(NEW.sname,v_dept_name,sysdate);
	return NEW;
end
$$ language plpgsql;

【方法3】
create or replace function T_INS_F() returns trigger as 
$$
declare v_deptno int;
declare v_dept_name text;
begin
	v_deptno := NEW.deptno;
	v_dept_name := (select dept_name from dept where id=v_deptno);
	insert into logger(sname, dept_name, log_date) values (NEW.sname, v_dept_name, SYSDATE);
	return NEW;
end;
$$ language plpgsql;

2、创建触发器
create trigger T_INS_TR 
after insert on tab1 
for each row 
execute procedure T_INS_F();