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

Re: Trigger function to know which fields are being updated

From: "Bernard Cheung" <cheungsw(at)hotmail(dot)com>
To: jeff_eckermann(at)yahoo(dot)com, pgsql-sql(at)postgresql(dot)org
Subject: Re: Trigger function to know which fields are being updated
Date: 2004-05-11 07:09:49
Message-ID: BAY1-F47jnZ8SOyPWOi0000dbbc@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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


Responses

pgsql-sql by date

Next:From: Riccardo FacchiniDate: 2004-05-11 07:46:22
Subject: Re: Trigger function to know which fields are being updated
Previous:From: Hannu KrosingDate: 2004-05-11 06:59:42
Subject: Re: Adding MERGE to the TODO list (resend with subject)

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