Re: Trigger function to know which fields are being updated - Mailing list pgsql-sql

> --- Bernard Cheung <cheungsw@hotmail.com> 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 <jeff_eckermann@yahoo.com>
> > >To: Bernard Cheung <cheungsw@hotmail.com>,
> 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 <cheungsw@hotmail.com> 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 differencebetween 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.endif;regards,
 


=====
Riccardo G. Facchini