求助:各位兄台,谁有 PostgreSQL trigger 和存储过程的例子给一个.

zlj2208

最近有项目要用到 PostgreSQL ,原来系统中有很多 trigger, 按照 PostgreSQL 的官方文档,实验了N遍都不成功,已经被它折磨的无语了.
各位兄台,有使用过PostgreSQL的,请给一些Trigger,存储过程,用户函数的例子.
谢谢了!

renxiao2003

官方的文档中的不行吗？

zlj2208

自己搞定了!
我使用Aqua Data Studio 4.7 连接数据库,按照官方文档做实验,报各种错误, 后来同事在psql的命令行执行建立 triigger 和 function 的语句好用，然后就在命令行上做实验,问题解决了。
后来使用pgAdmin III图形工具也可以，不管怎么样问题解决了。下面是我的示例，希望对其他朋友有用。
Postgresql的例子太少了！那是相当少！

目的：对于基本表执行　insert,update,delete  在Log表中记录相关的操作内容。
1. 对基本表进行 insert 后会在log表中生成一条和基本表中相同的记录;
2. 对基本表进行 update 后会在log表中生成对基本表更新前和更新后2条记录;
3. 对基本表进行 delete 后会在log表中生成一条和基本表中相同的记录;
如果出现误操作可以通过log表来修正数据.

连接数据库工具: pgAdmin III
实验步骤:
1. 建立基本表和log表

1. create table testtable (
   
2.   id integer not null primary key ,
   
3.   sname varchar(100)
   
4. );
   
5. 
   
6. CREATE SEQUENCE seq_no1 MINVALUE 1;
   
7. 
   
8. CREATE TABLE logtesttable (
   
9.   logtestid INTEGER PRIMARY KEY DEFAULT NEXTVAL('seq_no1'),
   
10.   id INTEGER,
    
11.   sname VARCHAR(100),
    
12.   updt_type VARCHAR(20) ,
    
13.   updt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    
14. );

复制代码

2. 建立 Trigger 和 Function

1. ---------------------------------------------
   
2. --创建trigger调用的函数
   
3. --
   
4. CREATE OR REPLACE FUNCTION fun_trigger_insert()
   
5. RETURNS TRIGGER
   
6. LANGUAGE PLPGSQL
   
7. AS
   
8. $BODY$
   
9. BEGIN
   
10.   INSERT INTO logtesttable(id,sname,updt_type,updt_time)
    
11.   VALUES(new.id,new.sname,'insert',current_timestamp);
    
12. return new;
    
13. END;
    
14. $BODY$;
    
15. 
    
16. CREATE OR REPLACE FUNCTION fun_trigger_update()
    
17. RETURNS TRIGGER
    
18. LANGUAGE PLPGSQL
    
19. AS
    
20. $BODY$
    
21. BEGIN
    
22.   INSERT INTO logtesttable(id,sname,updt_type,updt_time)
    
23.   VALUES(OLD.id,OLD.sname,'before_update',current_timestamp);
    
24.   INSERT INTO logtesttable(id,sname,updt_type,updt_time)
    
25.   VALUES(new.id,new.sname,'after_update',current_timestamp);
    
26. return new;
    
27. END;
    
28. $BODY$;
    
29. 
    
30. CREATE OR REPLACE FUNCTION fun_trigger_delete()
    
31. RETURNS TRIGGER
    
32. LANGUAGE PLPGSQL
    
33. AS $BODY$
    
34. BEGIN
    
35.   INSERT INTO logtesttable(id,sname,updt_type,updt_time)
    
36.   VALUES(OLD.id,OLD.sname,'delete',current_timestamp);
    
37. return OLD;
    
38. END;
    
39. $BODY$;
    
40. 
    
41. -----------------------
    
42. --建立Trigger
    
43. --
    
44. CREATE TRIGGER TRIGGER_INSERT
    
45. AFTER INSERT ON testtable
    
46. FOR EACH ROW EXECUTE PROCEDURE fun_trigger_insert();
    
47. 
    
48. CREATE TRIGGER TRIGGER_UPDATE
    
49. AFTER UPDATE ON testtable
    
50. FOR EACH ROW EXECUTE PROCEDURE fun_trigger_update();
    
51. 
    
52. CREATE TRIGGER TRIGGER_DELETE
    
53. AFTER DELETE ON testtable
    
54. FOR EACH ROW EXECUTE PROCEDURE fun_trigger_delete();

复制代码

3. 检验Trigger是否生效
1). insert 操作

1. insert into testtable values (10,'aaaaa');
   
2. insert into testtable values (20,'bbbbb');

复制代码

查询数据库结果:

1. select * from testtable;
   
2. select * from logtesttable;
   
3. ------------------------
   
4. 输出结果:
   
5. ------------------------
   
6. id     sname   
   
7. -----  --------
   
8. 10     aaaaa   
   
9. 20     bbbbb   
   
10. 
    
11. 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    
12. 
    
13. [Executed: 11-8-26 上午09时49分05秒 ] [Execution: 0/ms]
    
14. 
    
15. logtestid     id     sname     updt_type     updt_time                  
    
16. ------------  -----  --------  ------------  --------------------------
    
17. 1             10     aaaaa     insert        2011-08-26 09:42:14.711853
    
18. 2             20     bbbbb     insert        2011-08-26 09:42:14.711853
    
19. 
    
20. 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    
21. 
    
22. [Executed: 11-8-26 上午09时49分05秒 ] [Execution: 0/ms]
    
23. 
    

复制代码

2). update 操作

1. update testtable set sname='xxxxxx' where id=20;

复制代码

查询数据库结果:

1. select * from testtable where id=20;
   
2. select * from logtesttable where id=20;
   
3. ------------------------
   
4. 输出结果:
   
5. ------------------------
   
6. id     sname   
   
7. -----  --------
   
8. 10     aaaaa   
   
9. 20     xxxxxx   
   
10. 
    
11. 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    
12. 
    
13. [Executed: 11-8-26 上午09时49分55秒 ] [Execution: 0/ms]
    
14. 
    
15. logtestid     id     sname     updt_type      updt_time                  
    
16. ------------  -----  --------  -------------  --------------------------
    
17. 1             10     aaaaa     insert         2011-08-26 09:42:14.711853
    
18. 2             20     bbbbb     insert         2011-08-26 09:42:14.711853
    
19. 3             20     bbbbb     before_update  2011-08-26 09:43:21.574783
    
20. 4             20     xxxxxx    after_update   2011-08-26 09:43:21.574783
    
21. 
    
22. 4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    
23. 
    
24. [Executed: 11-8-26 上午09时49分55秒 ] [Execution: 0/ms]
    

复制代码

3). delete 操作

1. delete from testtable where id=20;

复制代码

查询数据库结果:

1. select * from testtable;
   
2. select * from logtesttable;
   
3. ------------------------
   
4. 输出结果:
   
5. ------------------------
   
6. id     sname   
   
7. -----  --------
   
8. 10     aaaaa   
   
9. 
   
10. 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    
11. 
    
12. [Executed: 11-8-26 上午09时51分21秒 ] [Execution: 0/ms]
    
13. 
    
14. logtestid     id     sname     updt_type      updt_time                  
    
15. ------------  -----  --------  -------------  --------------------------
    
16. 1             10     aaaaa     insert         2011-08-26 09:42:14.711853
    
17. 2             20     bbbbb     insert         2011-08-26 09:42:14.711853
    
18. 3             20     bbbbb     before_update  2011-08-26 09:43:21.574783
    
19. 4             20     xxxxxx    after_update   2011-08-26 09:43:21.574783
    
20. 5             20     xxxxxx    delete         2011-08-26 09:44:44.509704
    
21. 
    
22. 5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
    
23. 
    
24. [Executed: 11-8-26 上午09时51分21秒 ] [Execution: 16/ms]
    

复制代码

RuralHunter

多谢楼主，很不错的例子