postgresql用一个转账的小例子验证使用trigger时before与after区别

eg:

table_name:bank--记录用户的账户余额
Table "public.bank"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
name | character varying | | | | extended | |
sal | numeric | | | | main | |
Triggers:
bank_trigger BEFORE UPDATE ON bank FOR EACH ROW EXECUTE FUNCTION bank_t()
------------------------------------------------------- 
table_name:bank_log--记录操作日志
Table "public.bank_log"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('bank_log_id_seq'::regclass) | plain | |
time | timestamp without time zone | | | | plain | |
log | character varying | | | | extended | |
sal | numeric | | | | main | |
-------------------------------------------------------
function:transfer--实现转账操作
CREATE OR REPLACE FUNCTION public.transfer(out_name character varying, in_name character varying, num numeric)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
sal numeric;
result varchar;
sql varchar;
BEGIN
sql := 'select sal from bank where name='''||out_name||'''';
execute sql into sal;
if sal is null then
raise notice '% not exists',out_name;
return 0;
else
if sal < num then
raise notice '% not have enough money,now have %',out_name,sal;
return 0;
else
sql := 'select sal from bank where name='''||in_name||'''';
execute sql into sal;
if sal is null then
raise notice '% not exists',in_name;
return 0;
else
sql := 'update bank set sal=sal-'||num||' where name='''||out_name||'''';
execute sql;
sql := 'update bank set sal=sal+'||num||' where name='''||in_name||'''';
execute sql;
return 1;
end if;
end if;
end if;
end;
$function$;
-------------------------------------------------------
function:bank_t--触发器函数
CREATE OR REPLACE FUNCTION public.bank_t()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
if TG_OP = 'UPDATE' then
insert into bank_log(time,log,sal) values(localtimestamp,new.name||' '||old.sal||' to '||new.sal,new.sal-old.sal);
end if;
return new;
end;
$function$;
-------------------------------------------------------

执行结果：libai余额减2.5，guanyu余额加2.5，并记录bank_log

修改function:bank_t中return new;为reutrn null;
执行结果：余额未发生变化，记录bank_log

修改trigger中before为after
执行结果：libai余额减2.5，guanyu余额加2.5，并记录bank_log

结论：
trigger语句中after是在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；
before是在记录操纵之前触发，是先完成触发，再增删改，触发的语句先于监视的增删改。
触发器使用before记录日志表时，调用的触发器函数中需使用return new; 不可以return null;否则增删改查不会被执行。