Re: Trigger function to know which fields are being updated

> --- Bernard Cheung <cheungsw(at)hotmail(dot)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(at)yahoo(dot)com>
> > >To: Bernard Cheung <cheungsw(at)hotmail(dot)com>,
> pgsql-sql(at)postgresql(dot)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(at)hotmail(dot)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(at)postgresql(dot)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