pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger on a column
3+ messages / 2 participants
[nested] [flat]
* Trigger on a column
@ 2006-02-06 09:44 Josep Sanmartí <josep.sanmarti@openwired.net>
0 siblings, 1 reply; 3+ messages in thread
From: Josep Sanmartí @ 2006-02-06 09:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
My trigger's running on a table that works perfectly. This trigger calls
a function that inserts or modifies a row of a different table. My
problem is that I need to optimize this trigger, and the only way that
I've found is to fire the trigger when certain table fields are
modified. I do it like this:
CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE OF status
ON am_access_point EXECUTE PROCEDURE f_barra_aps();
but I get this error:
ERROR: syntax error at or near "status" at character 62
The status field is correct. Can anybody help me?
Thanks
--
Josep Sanmarti
Analista de Projectes
OpenWired
Caballero 87 - Bajos
08029 - Barcelona
Tel. 93 495 0990
Fax. 93 419 4591
Openwired
Alejandro Villegas,29
28043 - MADRID - ESPAÑA
Teléfono: 91 300 51 09
Fax: 91 300 28 13
http://www.openwired.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger on a column
@ 2006-02-06 10:48 Josep Sanmartí <josep.sanmarti@openwired.net>
parent: Josep Sanmartí <josep.sanmarti@openwired.net>
0 siblings, 1 reply; 3+ messages in thread
From: Josep Sanmartí @ 2006-02-06 10:48 UTC (permalink / raw)
To: William Leite Araújo <william.bh@gmail.com>; pgsql-sql
Hi,
readding around I just found this:
The CREATE TRIGGER statement in PostgreSQL implements a subset of the
SQL99 standard. (There are no provisions for triggers in SQL92.) The
following functionality IS MISSING:
* SQL99 allows triggers to fire on updates to specific columns (e.g.,
AFTER UPDATE OF col1, col2).
* .....
Does anybody know if that's true on version postgresql (PostgreSQL)
8.1.2? Because I don't actually know from what version that sentence is.
Thanks in advance
William Leite Araújo wrote:
> http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.html :
>
> CREATE TRIGGER -- define a new trigger
>
>
> Synopsis
>
>CREATE TRIGGER /name/ { BEFORE | AFTER } { /event/ [ OR ... ] }
>
> ON /table/ [ FOR [ EACH ] { ROW | STATEMENT } ]
> EXECUTE PROCEDURE /funcname/ ( /arguments/ )
>
>
> There is not a way to create column level trigger. If the trigger
> don't block the insert/update/delete, you can try an "after trigger"
> that do all insertions once.
>
> 2006/2/6, Josep Sanmartí <josep.sanmarti@openwired.net
> <mailto:josep.sanmarti@openwired.net>>:
>
> Hi,
>
> My trigger's running on a table that works perfectly. This trigger
> calls
> a function that inserts or modifies a row of a different table. My
> problem is that I need to optimize this trigger, and the only way
> that
> I've found is to fire the trigger when certain table fields are
> modified. I do it like this:
>
> CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE OF status
> ON am_access_point EXECUTE PROCEDURE f_barra_aps();
>
> but I get this error:
> ERROR: syntax error at or near "status" at character 62
>
> The status field is correct. Can anybody help me?
>
> Thanks
>
> --
> Josep Sanmarti
> Analista de Projectes
>
> OpenWired
> Caballero 87 - Bajos
> 08029 - Barcelona
> Tel. 93 495 0990
> Fax. 93 419 4591
>
> Openwired
> Alejandro Villegas,29
> 28043 - MADRID - ESPAÑA
> Teléfono: 91 300 51 09
> Fax: 91 300 28 13
> http://www.openwired.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>
>
> --
> William Leite Araújo
--
Josep Sanmarti
Analista de Projectes
OpenWired
Caballero 87 - Bajos
08029 - Barcelona
Tel. 93 495 0990
Fax. 93 419 4591
Openwired
Alejandro Villegas,29
28043 - MADRID - ESPAÑA
Teléfono: 91 300 51 09
Fax: 91 300 28 13
http://www.openwired.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger on a column
@ 2006-02-06 11:01 Richard Huxton <dev@archonet.com>
parent: Josep Sanmartí <josep.sanmarti@openwired.net>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2006-02-06 11:01 UTC (permalink / raw)
To: Josep Sanmartí <josep.sanmarti@openwired.net>; +Cc: William Leite Araújo <william.bh@gmail.com>; pgsql-sql
Josep Sanmartí wrote:
> Hi,
>
> readding around I just found this:
> The CREATE TRIGGER statement in PostgreSQL implements a subset of the
> SQL99 standard. (There are no provisions for triggers in SQL92.) The
> following functionality IS MISSING:
> * SQL99 allows triggers to fire on updates to specific columns (e.g.,
> AFTER UPDATE OF col1, col2).
> * .....
>
> Does anybody know if that's true on version postgresql (PostgreSQL)
> 8.1.2? Because I don't actually know from what version that sentence is.
Yes it is true for the current version.
Up-to-date documentation always ships with a release and is also
available on the website.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-02-06 11:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-06 09:44 Trigger on a column Josep Sanmartí <josep.sanmarti@openwired.net>
2006-02-06 10:48 ` Josep Sanmartí <josep.sanmarti@openwired.net>
2006-02-06 11:01 ` Richard Huxton <dev@archonet.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