pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger function to know which fields are being updated
5+ messages / 3 participants
[nested] [flat]
* Trigger function to know which fields are being updated
@ 2004-05-10 02:47 Bernard Cheung <cheungsw@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bernard Cheung @ 2004-05-10 02:47 UTC (permalink / raw)
To: pgsql-sql
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;
Are there any functions like the Oracle's UPDATING() predicate?
Bernard Cheung
_________________________________________________________________
Linguaphone : Learning English? Get Japanese lessons for FREE
http://go.msnserver.com/HK/46165.asp
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger function to know which fields are being updated
@ 2004-05-10 21:38 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Bernard Cheung <cheungsw@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Eckermann @ 2004-05-10 21:38 UTC (permalink / raw)
To: Bernard Cheung <cheungsw@hotmail.com>; pgsql-sql
--- 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
>
>
_________________________________________________________________
> Linguaphone : Learning English? Get Japanese
> lessons for FREE
> http://go.msnserver.com/HK/46165.asp
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger function to know which fields are being updated
@ 2004-05-11 07:09 Bernard Cheung <cheungsw@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bernard Cheung @ 2004-05-11 07:09 UTC (permalink / raw)
To: jeff_eckermann@yahoo.com; 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@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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger function to know which fields are being updated
@ 2004-05-11 07:46 Riccardo Facchini <facchini@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Riccardo Facchini @ 2004-05-11 07:46 UTC (permalink / raw)
To: pgsql-sql
> --- Bernard Cheung <cheungsw@hotmail.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@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
> >
> >
> > ---------------------------(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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger function to know which fields are being updated
@ 2004-05-11 13:59 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Bernard Cheung <cheungsw@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Eckermann @ 2004-05-11 13:59 UTC (permalink / raw)
To: Bernard Cheung <cheungsw@hotmail.com>; pgsql-sql
--- Bernard Cheung <cheungsw@hotmail.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.
In case 2 above, the updated record will keep the same
"name" value that it had previously. The logic that
you need to use will depend on precisely what you want
to happen. For example, if you expect that the name
must change, then you can test "NEW.NAME = OLD.NAME".
But if the name is not changing, this will not tell
you anything, even if the user is in fact supplying
the name.
Depending on what rules you want to enforce, you may
be better off doing the checking in your application.
>
> >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
>
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-05-11 13:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-10 02:47 Trigger function to know which fields are being updated Bernard Cheung <cheungsw@hotmail.com>
2004-05-10 21:38 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-05-11 07:09 Re: Trigger function to know which fields are being updated Bernard Cheung <cheungsw@hotmail.com>
2004-05-11 13:59 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-05-11 07:46 Re: Trigger function to know which fields are being updated Riccardo Facchini <facchini@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox