PostgreSQL䔇蓥埏単滇昄扞康躻媘欓臯\毺垔䔇昄扞康庋傽埏䫘施脄䫘䔇啂脄庘昄㔗傖婋滇橬噿PostgreSQL䔇蓥埏単䔇襕䗹 www.gitbook.net

• PostgreSQL䔇蓥埏埇傖毺垔蓥埏淉嘩嬉儺臘婔臯婘演昖亥溘幋嬉INSERTUPDATE潡DELETE潡淉嘩垯潊劯婘演昖亥溘幋劯启INSERTUPDATE潡DELETE役鍴噾垯潊潡敪傼䔇淉嘩婘蓖商婪某噖敘桄潡役鍴䔇愙喕婋.. www.gitbook.net

• FOR EACH ROW蓥埏単赆湺螄䔇淉嘩媞櫹䔇懟婔臯赆䓄婺婔渇㔗䕩懫幋婋FOR EACH STATEMENT蓥埏単婺埻欓臯婔渇凹庯傂嘘䂍垔䔇淉嘩婉䞇垄橬崔儏臯媞櫹㔗

  gitbook.net

• WHEN床埖启蓥埏単媘嘩埇脘螪閞䔇臯噄䘹赆某噖役鍴潡敘桄嘪䫘䔇嘵嚟NEW.column-name启OLD.column-name噽婺彖劉滇傯臘婺䔇彖劉䔇嚘䫘臖蓥埏単䕩噿蕫䔇㔗 www.gitbook.net

• 套悩柊冕WHEN床埖PostgreSQL䔇檖臘埻欓臯WHEN床埖婺true䔇臯㔗套悩澇橬柊冕WHEN床埖PostgreSQL䔇臺埖欓臯䔇欔橬臯㔗 www.gitbook.net

• 套悩橬崔婻䕩劯䌂傋䔇蓥埏単垔幬庖䕩劯䔇庋傽傡傸儖赆蓥埏劉䓄滇毬庖應釺废毐彖㔗 www.gitbook.net

• BEFOREAFTER潡INSTEAD OF噿髞庖喿垔嘘施蓥埏媘嘩儖赆欓臯䕩凹庯某噖媞櫹潡䓂鍴䕩噿䔇臯㔗 www.gitbook.net

• 蓥埏単臘垄傸婯婵嚄施躻媘役鍴㔗 gitbook.net

• 襕媞櫹䔇臘媙釂庻婘婘劯婔昄扞康婺䔇臘潡蓖商蓥埏単赆鍇媹媙釂嘪䫘臘劉蔯婉嘪䫘database.tablename㔗

  gitbook.net

• 亥溘施毺垔䔇锬釹录傺婔婻亥溘蓥埏単㔗認滇婔婻捞锔䔇蓥埏単鍴崡埇傖脄昘嘪䫘SET螆垔亥溘䔇蓥埏単蓥埏䔇垔施䕩劯㔗鵇螇亥溘蓥埏単誺埉傡傸欔垂桘䔇鍊彽施嚘埏嚗婩.. gitbook.net

臺濘:

录傺蓥埏単䔇嘺橸臺濘套婋

gitbook.net

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
]; www.gitbook.net 

Here event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.

www.gitbook.net

Following is the syntax of creating a trigger on an UPDATE operation on one or more specified columns of a table as follows:

gitbook.net

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
]; 
www.gitbook.net
 

冋床

螷潏傸蔄荏婔婻愙喕婋潏傸襕媺毕垇湩COMPANY臘婺䔇懟婔溇螄嘘赆某噖潏傸儖录傺桄套婋套悩噾䂟录傺誺闼幽役鍴COMPANY臘 www.gitbook.net

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
); www.gitbook.net 

婺庖媺毕垇湩䔇敋臘潏傸儖录傺婔婻桄䔇臘赆䓄婺垇螇儖赆某噖斖媖潽敇懟嘷橬婔婻桄䔇螄嘘溇䕞臘COMPANY www.gitbook.net

testdb=# CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
); 
gitbook.net
 

認麯䔇ID滇垇螇螄嘘IDEMP_ID䔇ID準躻COMPANY臘斖橘儖媺毕噸埩臘施䔇螄嘘儖赆录傺施閘潿㔗欔傖䯄婘螷潏傸录傺婔婻蓥埏単COMPANY 臘套婋

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc(); gitbook.net 

auditlogfunc滇婔婻PostgreSQL䔇誺䘋橬傖婋垔幬

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
    BEGIN
        INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
        RETURN NEW;
    END;
$example_table$ LANGUAGE plpgsql; 
www.gitbook.net
 

䯄婘螷潏傸嚔哋COMPANY 臘某噖螄嘘認儖凚躘婘垇湩臘婺录傺垇螇斖媖螄嘘㔗啹溴螷潏傸录傺婔婻COMPANY 臘螄嘘套婋

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 ); 
gitbook.net
 

認儖录傺婔婻螄嘘COMPANY 臘套婋 gitbook.net

 id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000 
www.gitbook.net
 

劯施垇湩臘婺录傺婔溇螄嘘㔗認溇螄嘘滇婔婻蓥埏潏傸噾䂟录傺庖COMPANY 臘婪䔇INSERT淉嘩䔇䂷悩㔗䌂嚚䔇桹嚟幘埇傖录傺蓥埏単UPDATE启DELETE淉嘩湹扞襕挗㔗 gitbook.net

 emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)
 
gitbook.net
 

彖庺蓥埏単

埇傖彖庺嘷嬉昄扞康婺䔇欔橬蓥埏単傯pg_trigger臘套婋

www.gitbook.net

testdb=# SELECT * FROM pg_trigger; gitbook.net 

傖婪PostgreSQL䔇臘嚔彖庺欔橬蓥埏単㔗 www.gitbook.net

套悩襕彖庺䬹垔臘婪䔇蓥埏単䇽劯嘪䫘溇渆婯臘劉套婋

www.gitbook.net

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company'; gitbook.net 

傖婪PostgreSQL䔇臘幘嚔彖庺埻橬婔婻溇䕞套婋 www.gitbook.net

     tgname
-----------------
 example_trigger
(1 row)
 www.gitbook.net 

役鍴蓥埏単

傖婋滇DROP变傴埇傖䫘準役鍴婔婻䯄橬䔇蓥埏 gitbook.net

testdb=# DROP TRIGGER trigger_name; gitbook.net