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.
Table dropped.
Table created.
Trigger created.
Trigger created.
Trigger created.
Trigger created.
insert action
1 row(s) inserted.
1 row(s) inserted.
search log
f_logid f_message f_operatedate
1 insert:tv 07/05/2021
2 insert:a/c 07/05/2021
2 row(s) retrieved.
update action
1 row(s) updated.
search log
f_logid f_message f_operatedate
1 insert:tv 07/05/2021
2 insert:a/c 07/05/2021
3 update: f_qty->10 07/05/2021
3 row(s) retrieved.
select action
f_saleid f_productname f_qty
1 tv 15
2 a/c 20
2 row(s) retrieved.
search log
f_logid f_message f_operatedate
1 insert:tv 07/05/2021
2 insert:a/c 07/05/2021
3 update: f_qty->10 07/05/2021
4 select:tv 07/05/2021
5 select:a/c 07/05/2021
5 row(s) retrieved.
delete action
1 row(s) deleted.
search log
f_logid f_message f_operatedate
1 insert:tv 07/05/2021
2 insert:a/c 07/05/2021
3 update: f_qty->10 07/05/2021
4 select:tv 07/05/2021
5 select:a/c 07/05/2021
6 delete:f_saleid->1 07/05/2021
6 row(s) retrieved.
select action
f_saleid f_productname f_qty
2 a/c 20
1 row(s) retrieved.
search log
f_logid f_message f_operatedate
1 insert:tv 07/05/2021
2 insert:a/c 07/05/2021
3 update: f_qty->10 07/05/2021
4 select:tv 07/05/2021
5 select:a/c 07/05/2021
6 delete:f_saleid->1 07/05/2021
7 select:a/c 07/05/2021
7 row(s) retrieved.
Database closed.
[gbasedbt@devsvr train]$
GBASE南大通用技术分享：删除触发器
drop trigger <trigger_name>;
DEMO
drop trigger if exists trg_sale_insert;
drop trigger if exists trg_sale_update;
drop trigger if exists trg_sale_delete;
drop trigger if exists trg_sale_select;
> drop trigger if exists trg_sale_insert;
Trigger dropped.
> drop trigger if exists trg_sale_update;
Trigger dropped.
> drop trigger if exists trg_sale_delete;
Trigger dropped.
> drop trigger if exists trg_sale_select;
Trigger dropped.
>
GBASE南大通用技术分享：DML
insert
insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] values(value1, value2, ...);
insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] select col_name1, col_name2, ... ;
insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] execute function <fn_name([param1, param2, ...])>;
DEMO
drop table if exists t_user1;
create table t_user1(f_userid int, f_username varchar(20));
insert into t_user1 values(1, 'gbasedbt');
select * from t_user1;
drop table if exists t_user2;
create table t_user2(f_userid int, f_username varchar(20));
insert into t_user2 select * from t_user1;
select * from t_user2;
drop table if exists t_user3;
create table t_user3(f_userid int, f_username varchar(20));
drop function if exists fn_user_add;
create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username
define i int;
define userid int;
define username varchar(20);
for i = 1 to user_num
let userid = i;
let username = concat('user_', to_char(i));
return userid, username with resume;
end for;
end function;
insert into t_user3 execute function fn_user_add(10);
select * from t_user3;
update
update <table_name | view_name | synonym_name> set column_name1 = value1[, column_name2 = value2, ... ] [where condition];
DEMO
drop table if exists t_user;
create table t_user(f_userid int, f_username varchar(50), f_age int);
insert into t_user values(1, 'Mary', 18);
insert into t_user values(2, 'Jack', 21);
select * from t_user;
update t_user set f_age = 20 where f_userid = 1;
select * from t_user;
> drop table if exists t_user;
Table dropped.
> create table t_user(f_userid int, f_username varchar(50), f_age int);
Table created.
> insert into t_user values(1, 'Mary', 18);
1 row(s) inserted.
> insert into t_user values(2, 'Jack', 21);
1 row(s) inserted.
> select * from t_user;
f_userid f_username f_age
1 Mary 18
2 Jack 21
2 row(s) retrieved.
> update t_user set f_age = 20 where f_userid = 1;
1 row(s) updated.
> select * from t_user;
f_userid f_username f_age
1 Mary 20
2 Jack 21
2 row(s) retrieved.
>
delete
delete from <table_name | view_name | synonym_name> [where condition];
DEMO
select * from t_user;
delete from t_user where f_userid = 1;
select * from t_user;
> select * from t_user;
f_userid f_username f_age
1 Mary 20
2 Jack 21
2 row(s) retrieved.
>
>
>
> select * from t_user;
f_userid f_username f_age
1 Mary 20
2 Jack 21
2 row(s) retrieved.
> delete from t_user where f_userid = 1;
1 row(s) deleted.
> select * from t_user;
f_userid f_username f_age
2 Jack 21
1 row(s) retrieved.
>
merge
merge into <target_table_name> as t
using <source_table_name | source_query> as s
on t.column_name1 = s.column_name2
when matched then update set t.col_1 = s.col_1, t.col_2 = s.col_2, ...
when not matched then insert (t.col_1, t.col_2, ...) values(s.col_1, s.col_2, ...);
DEMO
drop table if exists t_user1;
create table t_user1(f_userid int, f_username varchar(20), f_age int);
insert into t_user1 values(1, 'Tom', 28);
insert into t_user1 values(2, 'Jack', 26);
insert into t_user1 values(4, 'Rose', 18);
select * from t_user1;
drop table if exists t_user2;
create table t_user2(f_userid int, f_username varchar(20), f_age int);
insert into t_user2 values(3, 'Jim', 25);
insert into t_user2 values(4, 'Rose', 23);
insert into t_user2 values(5, 'Mike', 21);
insert into t_user2 values(6, 'Bill', 19);
select * from t_user2;
merge into t_user1 a
using t_user2 b
on a.f_userid = b.f_userid
when matched then update set a.f_age = b.f_age
when not matched then insert (a.f_userid, a.f_username, a.f_age) values(b.f_userid, b.f_username, b.f_age);
select * from t_user1;
> drop table if exists t_user1;
Table dropped.
> create table t_user1(f_userid int, f_username varchar(20), f_age int);
Table created.
> insert into t_user1 values(1, 'Tom', 28);
1 row(s) inserted.
> insert into t_user1 values(2, 'Jack', 26);
1 row(s) inserted.
> insert into t_user1 values(4, 'Rose', 18);
1 row(s) inserted.
> select * from t_user1;
f_userid f_username f_age
1 Tom 28
2 Jack 26
4 Rose 18
3 row(s) retrieved.
> drop table if exists t_user2;
Table dropped.
> create table t_user2(f_userid int, f_username varchar(20), f_age int);
Table created.
> insert into t_user2 values(3, 'Jim', 25);
1 row(s) inserted.
> insert into t_user2 values(4, 'Rose', 23);
1 row(s) inserted.
> insert into t_user2 values(5, 'Mike', 21);
1 row(s) inserted.
> insert into t_user2 values(6, 'Bill', 19);
1 row(s) inserted.
> select * from t_user2;
f_userid f_username f_age
3 Jim 25
4 Rose 23
5 Mike 21
6 Bill 19
4 row(s) retrieved.
> merge into t_user1 a
> using t_user2 b
> on a.f_userid = b.f_userid
> when matched then update set a.f_age = b.f_age
> when not matched then insert (a.f_userid, a.f_username, a.f_age) values(b.f_userid, b.f_username, b.f_age);
4 row(s) merged.
> select * from t_user1;
f_userid f_username f_age
1 Tom 28
2 Jack 26
4 Rose 23
3 Jim 25
5 Mike 21
6 Bill 19
6 row(s) retrieved.
>
548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
