Skip site navigation (1) Skip section navigation (2)

Re: Post Gres triggers for trail

From: "Jason Minion" <jason(dot)minion(at)sigler(dot)com>
To: "Mandar M Bhosale" <mbhosale(at)cybage(dot)com>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Post Gres triggers for trail
Date: 2005-05-02 12:41:23
Message-ID: 0105A1BF505D304E9E5AF38B63E40E4E807A25@exchange.siglercompanies.com (view raw or flat)
Thread:
Lists: pgsql-admin
You probably want to run the trigger "BEFORE UPDATE" unless you have some other code depending on the value of modified_date during the update, and also instead of "RETURN NULL;" do a "RETURN NEW;". Here's an example from one of my triggers:
 
Trigger definition:
CREATE TRIGGER trig_rec_returndate
  BEFORE UPDATE
  ON rec_main
  FOR EACH ROW
  EXECUTE PROCEDURE fun_rec_returndate();
 
Trigger function definition:
CREATE OR REPLACE FUNCTION fun_rec_returndate()
  RETURNS "trigger" AS
'BEGIN
 IF NEW.returncode IS NOT NULL THEN
  IF OLD.returndate IS NULL THEN
   NEW.returndate := \'now\';
  END IF;
 END IF;
 RETURN NEW;
END;'
  LANGUAGE 'plpgsql' VOLATILE;
 
In that case returndate is a timestamp. Hope it helps!
 
Jason Minion

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Mandar M Bhosale
Sent: Monday, April 25, 2005 9:36 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] Post Gres triggers for trail


I have difficulty in setting a trigger to have trail of my project 
 
I need to update the modified_date of my database tables through trigger as
 
CREATE OR REPLACE TRIGGER taril_trigger AFTER UPDATE ON test_trigger FOR EACH ROW
EXECUTE PROCEDURE pro_trail();
 
pro_trail(){
BEGIN 
NEW.modified_date=(select current_timestamp)
return NULL;
END;
}

Can anybody help me out!!!

Mandar  M. Bhosale


pgsql-admin by date

Next:From: Tom LaneDate: 2005-05-02 14:52:47
Subject: Re: A nightmare
Previous:From: Holger HoffstaetteDate: 2005-05-02 11:36:56
Subject: Re: measuring most-expensive queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group