From mbhosale@cybage.com Mon Apr 25 14:37:34 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4998B53A2C for ; Mon, 25 Apr 2005 11:37:34 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97293-04 for ; Mon, 25 Apr 2005 14:37:25 +0000 (GMT) Received: from herald.cybage.com (herald.cybage.com [203.199.150.150]) by svr1.postgresql.org (Postfix) with ESMTP id 9A53153A12 for ; Mon, 25 Apr 2005 11:37:23 -0300 (ADT) Received: from MBHOSALE ([192.168.2.109]) by herald.cybage.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 25 Apr 2005 20:05:51 +0530 From: "Mandar M Bhosale" To: Subject: Post Gres triggers for trail Date: Mon, 25 Apr 2005 20:05:51 +0530 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0004_01C549D2.2E41EF40" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-OriginalArrivalTime: 25 Apr 2005 14:35:51.0824 (UTC) FILETIME=[148DF900:01C549A4] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.28 tagged_above=0 required=5 tests=HTML_60_70, HTML_MESSAGE, HTML_TITLE_EMPTY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200504/412 X-Sequence-Number: 17425 This is a multi-part message in MIME format. ------=_NextPart_000_0004_01C549D2.2E41EF40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0004_01C549D2.2E41EF40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have difficulty in setting a trigger = to have=20 trail of my project
 
I need to update the modified_date of = my database=20 tables through trigger as
 
CREATE OR REPLACE TRIGGER taril_trigger = AFTER=20 UPDATE ON test_trigger FOR EACH ROW
EXECUTE PROCEDURE=20 pro_trail();
 
pro_trail(){
BEGIN =
NEW.modified_date=3D(select=20 current_timestamp)
return NULL;
END;
}

Can anybody help me out!!!

Mandar  M. Bhosale

------=_NextPart_000_0004_01C549D2.2E41EF40-- From jason.minion@sigler.com Mon May 2 12:41:31 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CE6EB5294D for ; Mon, 2 May 2005 09:41:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89323-03 for ; Mon, 2 May 2005 12:41:27 +0000 (GMT) Received: from exchange.siglercompanies.com (sig-gw.isunet.net [63.175.165.221]) by svr1.postgresql.org (Postfix) with ESMTP id 04B8753977 for ; Mon, 2 May 2005 09:41:25 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C54F14.3FC4156F" Subject: Re: Post Gres triggers for trail Date: Mon, 2 May 2005 07:41:23 -0500 Message-ID: <0105A1BF505D304E9E5AF38B63E40E4E807A25@exchange.siglercompanies.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Post Gres triggers for trail Thread-Index: AcVNH/K0gGbLuikcSDOgZAGw3a0BdwB9GqCQ From: "Jason Minion" To: "Mandar M Bhosale" , X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.224 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, HTML_TITLE_EMPTY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200505/6 X-Sequence-Number: 17435 This is a multi-part message in MIME format. ------_=_NextPart_001_01C54F14.3FC4156F Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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: =20 Trigger definition: CREATE TRIGGER trig_rec_returndate BEFORE UPDATE ON rec_main FOR EACH ROW EXECUTE PROCEDURE fun_rec_returndate(); =20 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 :=3D \'now\'; END IF; END IF; RETURN NEW; END;' LANGUAGE 'plpgsql' VOLATILE; =20 In that case returndate is a timestamp. Hope it helps! =20 Jason Minion -----Original Message----- From: pgsql-admin-owner@postgresql.org = [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Mandar M Bhosale Sent: Monday, April 25, 2005 9:36 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Post Gres triggers for trail I have difficulty in setting a trigger to have trail of my project=20 =20 I need to update the modified_date of my database tables through trigger = as =20 CREATE OR REPLACE TRIGGER taril_trigger AFTER UPDATE ON test_trigger FOR = EACH ROW EXECUTE PROCEDURE pro_trail(); =20 pro_trail(){ BEGIN=20 NEW.modified_date=3D(select current_timestamp) return NULL; END; } Can anybody help me out!!! Mandar M. Bhosale ------_=_NextPart_001_01C54F14.3FC4156F Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
You = probably want to=20 run the trigger "BEFORE UPDATE" unless you have some other code = depending on the=20 value of modified_date during the update, and also instead of "RETURN = NULL;" do=20 a "RETURN NEW;". Here's an example from one of my = triggers:
 
Trigger=20 definition:
CREATE = TRIGGER=20 trig_rec_returndate
  BEFORE UPDATE
  ON = rec_main
  FOR=20 EACH ROW
  EXECUTE PROCEDURE = fun_rec_returndate();
 
Trigger function=20 definition:
CREATE = OR REPLACE=20 FUNCTION fun_rec_returndate()
  RETURNS "trigger"=20 AS
'BEGIN
 IF NEW.returncode IS NOT NULL = THEN
  IF=20 OLD.returndate IS NULL THEN
   NEW.returndate :=3D=20 \'now\';
  END IF;
 END IF;
 RETURN=20 NEW;
END;'
  LANGUAGE 'plpgsql' VOLATILE;
 
In = that case=20 returndate is a timestamp. Hope it helps!
 
Jason Minion
-----Original Message-----
From:=20 pgsql-admin-owner@postgresql.org=20 [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Mandar M=20 Bhosale
Sent: Monday, April 25, 2005 9:36 AM
To:=20 pgsql-admin@postgresql.org
Subject: [ADMIN] Post Gres = triggers for=20 trail

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

Can anybody help me out!!!

Mandar  M.=20 Bhosale

------_=_NextPart_001_01C54F14.3FC4156F--