From: "Bernard Cheung" <cheungsw@hotmail.com>
To: jeff_eckermann@yahoo.com, pgsql-sql@postgresql.org
Subject: Re: Trigger function to know which fields are being updated
Date: Tue, 11 May 2004 15:09:49 +0800
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY1-F47jnZ8SOyPWOi0000dbbc@hotmail.com>

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