求助:各位兄臺,誰有 PostgreSQL trigger 和存儲過程的例子給一個(gè).

zlj2208

最近有項(xiàng)目要用到 PostgreSQL ,原來系統(tǒng)中有很多 trigger, 按照 PostgreSQL 的官方文檔,實(shí)驗(yàn)了N遍都不成功,已經(jīng)被它折磨的無語了.
各位兄臺,有使用過PostgreSQL的,請給一些Trigger,存儲過程,用戶函數(shù)的例子.
謝謝了!

renxiao2003

官方的文檔中的不行嗎？

zlj2208

自己搞定了!
我使用Aqua Data Studio 4.7 連接數(shù)據(jù)庫,按照官方文檔做實(shí)驗(yàn),報(bào)各種錯(cuò)誤, 后來同事在psql的命令行執(zhí)行建立 triigger 和 function 的語句好用，然后就在命令行上做實(shí)驗(yàn),問題解決了。
后來使用pgAdmin III圖形工具也可以，不管怎么樣問題解決了。下面是我的示例，希望對其他朋友有用。
Postgresql的例子太少了！那是相當(dāng)少！

目的：對于基本表執(zhí)行　insert,update,delete  在Log表中記錄相關(guān)的操作內(nèi)容。
1. 對基本表進(jìn)行 insert 后會在log表中生成一條和基本表中相同的記錄;
2. 對基本表進(jìn)行 update 后會在log表中生成對基本表更新前和更新后2條記錄;
3. 對基本表進(jìn)行 delete 后會在log表中生成一條和基本表中相同的記錄;
如果出現(xiàn)誤操作可以通過log表來修正數(shù)據(jù).

連接數(shù)據(jù)庫工具: pgAdmin III
實(shí)驗(yàn)步驟:
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. );

復(fù)制代碼

2. 建立 Trigger 和 Function

1. ---------------------------------------------
   
2. --創(chuàng)建trigger調(diào)用的函數(shù)
   
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();

復(fù)制代碼

3. 檢驗(yàn)Trigger是否生效
1). insert 操作

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

復(fù)制代碼

查詢數(shù)據(jù)庫結(jié)果:

1. select * from testtable;
   
2. select * from logtesttable;
   
3. ------------------------
   
4. 輸出結(jié)果:
   
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時(shí)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時(shí)49分05秒 ] [Execution: 0/ms]
    
23. 
    

復(fù)制代碼

2). update 操作

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

復(fù)制代碼

查詢數(shù)據(jù)庫結(jié)果:

1. select * from testtable where id=20;
   
2. select * from logtesttable where id=20;
   
3. ------------------------
   
4. 輸出結(jié)果:
   
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時(shí)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時(shí)49分55秒 ] [Execution: 0/ms]
    

復(fù)制代碼

3). delete 操作

1. delete from testtable where id=20;

復(fù)制代碼

查詢數(shù)據(jù)庫結(jié)果:

1. select * from testtable;
   
2. select * from logtesttable;
   
3. ------------------------
   
4. 輸出結(jié)果:
   
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時(shí)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時(shí)51分21秒 ] [Execution: 16/ms]
    

復(fù)制代碼

RuralHunter

多謝樓主，很不錯(cuò)的例子