GBASE南大通用GBase 8s数据库基本语法（2）

GBASE南大通用技术分享：GBase 8s数据库基本语法

触发器

触发器三要素：

事件(Event)
对数据库的插入、删除、修改操作。
当声明的事件发生时，触发器开始工作。

条件(Condition)
当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件。如果条件成立，则触发器执行相应的动作，否则触发器不做任何事情。

动作规则(Action Role)
 

GBASE南大通用技术分享：新增触发器

create [or replace] tirgger <trigger_name> <insert | update [of column_name] | delete | select> on <target_table_name>
<before | after | for each row>
when <condition>
<action>

DEMO

[gbasedbt@devsvr train]$ cat trigger.sql 
drop table if exists t_log;

create table t_log(f_logid serial, f_message varchar(50), f_operatedate date);

drop table if exists t_sale;

create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int);

create or replace trigger trg_sale_insert insert on t_sale
referencing new as new_item
for each row 
(
insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
);


create or replace trigger trg_sale_update update of f_qty on t_sale
referencing old as old_item
for each row
(
insert into t_log(f_message, f_operatedate) values(concat('update: f_qty->', to_char(old_item.f_qty)), today)
);

create or replace trigger trg_sale_delete delete on t_sale
referencing old as old_item
for each row 
(
insert into t_log(f_message, f_operatedate) values(concat('delete:f_saleid->', to_char(old_item.f_saleid)), today)
);

create or replace trigger trg_sale_select select on t_sale
referencing old as old_item
for each row
(
insert into t_log(f_message, f_operatedate) values(concat('select:', old_item.f_productname), today)
);

!echo "insert action"

insert into t_sale(f_productname, f_qty) values('tv', 10);
insert into t_sale(f_productname, f_qty) values('a/c', 20);

!echo "search log"

select * from t_log;


!echo "update action"

update t_sale set f_qty = 15 where f_productname = 'tv';

!echo "search log"

select * from t_log;

!echo "select action"

select * from t_sale;

!echo "search log"

select * from t_log;

!echo "delete action"

delete from t_sale where f_productname = 'tv';

!echo "search log"

select * from t_log;

!echo "select action"

select * from t_sale;

!echo "search log"

select * from t_log;

[gbasedbt@devsvr train]$ dbaccess mydb trigger.sql 
Your evaluation license will expire on 2022-06-18 00:00:00

Database selected.


Table dropped.


Table created.


T