Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BA4CB9FB1A5 for ; Thu, 18 Jan 2007 23:16:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 16888-07 for ; Thu, 18 Jan 2007 23:16:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 1FE3E9FA2A6 for ; Thu, 18 Jan 2007 23:16:27 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id B7FAE3547F; Thu, 18 Jan 2007 19:16:26 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B65F53547E; Thu, 18 Jan 2007 19:16:26 -0800 (PST) Date: Thu, 18 Jan 2007 19:16:26 -0800 (PST) From: Stephan Szabo To: "Negandhi, Nishith" Cc: pgsql-admin@postgresql.org Subject: Re: Error in trigger In-Reply-To: Message-ID: <20070118191415.Y43232@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/168 X-Sequence-Number: 24324 On Thu, 18 Jan 2007, Negandhi, Nishith wrote: > Hi, > All my tables in the database have "CreateDate" and "LastUpdateDate" > columns. I need to create a trigger wherein on insert the "CreateDate" > column is inserted with the current date. On Update the "LastUpdateDate" > is inserted with the current date. > > I tried the insert trigger as mentioned below: > > CREATE FUNCTION "CreateDate_F"() RETURNS TRIGGER AS $CreateDate_T$ > BEGIN > insert into "CreateDate" values (CURRENT_TIMESTAMP); > END; > $CreateDate_T$ LANGUAGE plpgsql; > > CREATE TRIGGER "Create_Date_T" AFTER INSERT ON "CreateDate" > FOR EACH STATEMENT EXECUTE PROCEDURE "CreateDate_F"(); I think you really want to be using a before row trigger and setting NEW."CreateDate" in it if you want each row to have the appropriate creation date (and a similar thing for the update date).