X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 39435D1B236 for ; Tue, 11 May 2004 04:46:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 28757-02 for ; Tue, 11 May 2004 04:46:23 -0300 (ADT) Received: from web13902.mail.yahoo.com (web13902.mail.yahoo.com [216.136.175.28]) by svr1.postgresql.org (Postfix) with SMTP id 4F561D1B32C for ; Tue, 11 May 2004 04:46:21 -0300 (ADT) Message-ID: <20040511074622.64446.qmail@web13902.mail.yahoo.com> Received: from [80.67.100.162] by web13902.mail.yahoo.com via HTTP; Tue, 11 May 2004 00:46:22 PDT Date: Tue, 11 May 2004 00:46:22 -0700 (PDT) From: Riccardo Facchini Subject: Re: Trigger function to know which fields are being updated To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=FOR_FREE X-Spam-Level: X-Archive-Number: 200405/96 X-Sequence-Number: 17810 > --- Bernard Cheung wrote: > > Thank you, but my intension is to check whether the user supplies > > NAME when > > updating the record. > > > > For example the trigger shall allow statement 1 and block statement > > 2: > > > > 1. UPDATE COMPANY SET NAME = 'ABC', ADDRESS = '123 Drive' WHERE > > COMPANY_ID = > > 1; > > > > 2. UPDATE COMPANY SET ADDRESS = '123 Drive' WHERE COMPANY_ID = 1; > > > > I want the trigger to ensure that the user must provide value for > > NAME when > > updating this record. > > > > >From: Jeff Eckermann > > >To: Bernard Cheung , > pgsql-sql@postgresql.org > > >Subject: Re: [SQL] Trigger function to know which fields are being > > updated > > >Date: Mon, 10 May 2004 14:38:56 -0700 (PDT) > > > > > > > > >--- Bernard Cheung wrote: > > > > I am writing a trigger function. How can I know > > > > which fields are being > > > > updated in the PL/SQL function? > > > > > > > > For example I have a table here: > > > > > > > > > > > > CREATE TABLE COMPANY ( > > > > COMPANY_ID VARCHAR(10) NOT NULL, > > > > NAME VARCHAR(30), > > > > ADDRESS VARCHAR(30)); > > > > > > > > I want to write a trigger to block all update > > > > statements without updating > > > > NAME. I tried the following code block and it > > > > doesn't work: > > > > > > > > > > > > IF TG_OP = ''UPDATE'' THEN > > > > IF NEW.NAME IS NULL THEN > > > > RAISE NOTICE ''Field NAME must be > > > > provided!''; > > > > END IF; > > > > END IF; > > > > > >That should work. Perhaps "name" is not actually > > >null, but rather an empty string? In that case, your > > >test needs to be: "IF NEW.NAME IS NULL OR NEW.NAME = > > >'''' THEN..." > > > > > > > > > > > Are there any functions like the Oracle's UPDATING() > > > > predicate? > > > > > > > > Bernard Cheung > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- > > > > TIP 1: subscribe and unsubscribe commands go to > > >majordomo@postgresql.org > > > > _________________________________________________________________ > > Linguaphone : Learning English? Get Japanese lessons for FREE > > http://go.msnserver.com/HK/46165.asp > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > One way that comes to my mind is to check if is there any difference between the new.name and old.name... something like: if (old.name <> new.name) then -- something happening here... else -- nothing happened, or the name has not been changed. end if; regards, ===== Riccardo G. Facchini