Skip site navigation (1) Skip section navigation (2)

Re: Trigger function to know which fields are being updated

From: Riccardo Facchini <facchini(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Trigger function to know which fields are being updated
Date: 2004-05-11 07:46:22
Message-ID: 20040511074622.64446.qmail@web13902.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
> --- 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

pgsql-sql by date

Next:From: Willem de JongDate: 2004-05-11 08:08:34
Subject: Seconds To Time
Previous:From: Bernard CheungDate: 2004-05-11 07:09:49
Subject: Re: Trigger function to know which fields are being updated

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group