触发器创建表日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
一、创建自增序列

id serial  [特殊的自增类型]
比如如下即可使用
create table test_b
(
  id serial PRIMARY KEY,
  name character varying(128)
); 
CREATE SEQUENCE sequencename
    [ INCREMENT increment ]        -- 自增数，默认是 1
    [ MINVALUE minvalue ]      -- 最小值
    [ MAXVALUE maxvalue ]      -- 最大值
    [ START start ]                -- 设置起始值
    [ CACHE cache ]                -- 是否预先缓存
    [ CYCLE ]                   -- 是否到达最大值的时候，重新返回到最小值


create  sequence c_id
start with 1
increment by 1
no minvalue
no maxvalue
cache 1;

在sequence中，如果是no minvalue、no maxvalue表示最小最大值为-1，注意最小值不能大于最大值。

create table t1_log (
id int,
update_time timestamp,
db_user varchar(40),
opr_type varchar(6),
t1_id int,
t1_name text
);

二、设置自定义自增列
alter table t1_log alter column id set default nextval('c_id');
或者：create table t1_log (id int default nextval ('c_id'),name text);

三、创建测试表
create table t1 (
id int,
name text
);

四、创建功能函数
create function log_t1_trigger()
returns trigger
language plpgsql
as $$
begin
if (TG_OP = 'INSERT')  then
insert into t1_log (update_time,db_user,opr_type,t1_id,t1_name) values (now(),user,TG_OP,new.id,new.name);
elsif (TG_OP = 'UPDATE') then
insert into t1_log (update_time,db_user,opr_type,t1_id,t1_name) values (now(),user,TG_OP,new.id,new.name);
else raise exception '非插入更新语句，你权力不够！请联系管理员！';
end if;
return null;
end;
$$;


五、创建触发器   statement(声明)
create trigger log_t1_trigger
after insert or delete or update on t1
for each row execute procedure log_t1_trigger();
六、测试
testdb=# insert into t1 values(1,'张三丰');
INSERT 0 1
testdb=# 
testdb=# insert into t1 values(1,'李二水');
INSERT 0 1
testdb=# update t1 set id=111 where name ='李二水';
UPDATE 1
testdb=# select * from t1;
 id  |  name  
-----+--------
   1 | 张三丰
 111 | 李二水
(2 rows)

testdb=# select * from t1_log;
 id |        update_time         | db_user | opr_type | t1_id | t1_name 
----+----------------------------+---------+----------+-------+---------
  5 | 2017-02-20 14:18:12.769588 | qwe     | INSERT   |     1 | 张三丰
  6 | 2017-02-20 14:18:33.298201 | qwe     | INSERT   |     1 | 李二水
  7 | 2017-02-20 14:19:43.28718  | qwe     | UPDATE   |   111 | 李二水
(3 rows)

testdb=# delete from t1 where id=111;
ERROR:  非插入更新语句，你权力不够！请联系管理员！



PS:为什么我的t1_log表id是从5开始的？
因为我在测试的时候删除了4次触发器函数，但是没有删除表t1_log，所以它是自动增长到5了。