pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers - need help !!!
6+ messages / 5 participants
[nested] [flat]
* Triggers - need help !!!
@ 2004-07-07 06:45 Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pradeepkumar, Pyatalo (IE10) @ 2004-07-07 06:45 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am writing some triggers for my project.
I am writing a trigger on a table after delete....I want to know how u refer
to the row that is being deleted.
For example for a trigger function after INSERT/UPDATE u refer to the row
being inserted/updated using NEW like this....
CREATE FUNCTION .....
BEGIN
IF UPDATE(CreateTime) THEN
SELECT INTO ValidCount COUNT(*) FROM PointTable
WHERE NEW.PointId = PointTable.PointId;
END IF;
......
....
END ;
' LANGUAGE 'plpgsql';
My question is how will refer to the row being deleted in the trigger
function.\
I want to do something like this
DELETE PointTable FROM PointTable
WHERE PointTable.PointId = DELETED.PointId;
Pls help me with this.
> With Best Regards
> Pradeep Kumar P J
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Triggers - need help !!!
@ 2004-07-07 07:20 Tomasz Myrta <jasiek@klaster.net>
parent: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2004-07-07 07:20 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-sql
On 2004-07-07 08:45, Użytkownik Pradeepkumar, Pyatalo (IE10) napisał:
> Hi,
>
> I am writing some triggers for my project.
> I am writing a trigger on a table after delete....I want to know how u refer
> to the row that is being deleted.
>
> For example for a trigger function after INSERT/UPDATE u refer to the row
> being inserted/updated using NEW like this....
Use OLD instead of NEW.
You can also use OLD inside UPDATE triggers - you have access to row
before update.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Triggers - need help !!!
@ 2004-07-07 08:48 Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pradeepkumar, Pyatalo (IE10) @ 2004-07-07 08:48 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-sql
Thanks a lot for ur help.
In the trigger, I am checking if a field is updated or not. The syntax I
use is
IF UPDATE(CreateTime) THEN
....
....
END IF;
Is this syntax correct.
-----Original Message-----
From: Tomasz Myrta [mailto:jasiek@klaster.net]
Sent: Wednesday, July 07, 2004 12:50 PM
To: Pradeepkumar, Pyatalo (IE10)
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Triggers - need help !!!
On 2004-07-07 08:45, Użytkownik Pradeepkumar, Pyatalo (IE10) napisał:
> Hi,
>
> I am writing some triggers for my project.
> I am writing a trigger on a table after delete....I want to know how u
refer
> to the row that is being deleted.
>
> For example for a trigger function after INSERT/UPDATE u refer to the row
> being inserted/updated using NEW like this....
Use OLD instead of NEW.
You can also use OLD inside UPDATE triggers - you have access to row
before update.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Triggers - need help !!!
@ 2004-07-07 11:03 Richard Huxton <dev@archonet.com>
parent: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 2 replies; 6+ messages in thread
From: Richard Huxton @ 2004-07-07 11:03 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
Pradeepkumar, Pyatalo (IE10) wrote:
> Thanks a lot for ur help.
> In the trigger, I am checking if a field is updated or not. The syntax I
> use is
>
> IF UPDATE(CreateTime) THEN
> ....
> ....
> END IF;
>
> Is this syntax correct.
No, and I don't recall seeing anything like it in the manuals.
IF OLD.CreateTime <> NEW.CreateTime THEN
...
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Triggers - need help !!!
@ 2004-07-07 12:43 Mischa Sandberg <mischa_sandberg@telus.net>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Mischa Sandberg @ 2004-07-07 12:43 UTC (permalink / raw)
To: pgsql-sql
"Richard Huxton" <dev@archonet.com> wrote in message
news:40EBD891.3050205@archonet.com...
> Pradeepkumar, Pyatalo (IE10) wrote:
> > IF UPDATE(CreateTime) THEN
> > ....
> > ....
> > END IF;
> >
> > Is this syntax correct.
>
> No, and I don't recall seeing anything like it in the manuals.
... and it's always an excellent time to read them ...
> IF OLD.CreateTime <> NEW.CreateTime THEN
> ...
... being mindful of the case where CreateTime might be NULL ...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Triggers - need help !!!
@ 2004-07-08 11:19 SZUCS Gábor <surrano@mailbox.hu>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: SZUCS Gábor @ 2004-07-08 11:19 UTC (permalink / raw)
To: pgsql-sql
I'd like to add that a NULL value might mess things up. If CreateTime may be
null, try this:
if (OLD.CreateTime <> NEW.CreateTime) OR
(OLD.CreateTime ISNULL <> NEW.CreateTime ISNULL) THEN ...
or this:
if COALESCE(OLD.CreateTime, '3001-01-01') <>
COALESCE(NEW.CreateTime, '3001-01-01') THEN ...
(provided you can safely assume that createtimes remain in this millenium
;) )
or maybe:
if COALESCE(OLD.CreateTime <> NEW.CreateTime,
OLD.CreateTime ISNULL <> NEW.CreateTime ISNULL) THEN ...
However; I'd stay with the first one. It's quite simple and Y3K-safe ;)
Also, it seems to be the most effective of them, if any.
G.
%----------------------- cut here -----------------------%
\end
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
Sent: Wednesday, July 07, 2004 1:03 PM
> Pradeepkumar, Pyatalo (IE10) wrote:
> > Thanks a lot for ur help.
> > In the trigger, I am checking if a field is updated or not. The syntax
I
> > use is
> >
> > IF UPDATE(CreateTime) THEN
> > ....
> > ....
> > END IF;
> >
> > Is this syntax correct.
>
> No, and I don't recall seeing anything like it in the manuals.
>
> IF OLD.CreateTime <> NEW.CreateTime THEN
> ...
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-07-08 11:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-07 06:45 Triggers - need help !!! Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-07-07 07:20 ` Tomasz Myrta <jasiek@klaster.net>
2004-07-07 08:48 Re: Triggers - need help !!! Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-07-07 11:03 ` Richard Huxton <dev@archonet.com>
2004-07-07 12:43 ` Mischa Sandberg <mischa_sandberg@telus.net>
2004-07-08 11:19 ` SZUCS Gábor <surrano@mailbox.hu>
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