Thread: Trigger on a column

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

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

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