Re: Trigger function to know which fields are being updated

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