On 3/21/06, Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> wrote:
On Tue, 21 Mar 2006, Larry White wrote:
> I can't figure out how to pass parameters to a trigger function.
>
> I checked the documentation and saw that trigger functions don't take
> params in the usual fashion,
> but couldn't find an example of a pl-sql trigger function that used
> the original row data within the function.
>
> What I want is an on update trigger that creates an entry in a second
> table. The second (history) table has a subset of the columns in the
> first.
>
> Here's what I have so far:
>
> -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION
>
> CREATE OR REPLACE FUNCTION audit_task ("param type declarations were
> here") RETURNS TRIGGER AS '
> -- create an audit trail record
> BEGIN
> -- Perform the insert
>
> INSERT INTO TASK_h (id,
> updated_by,
> updated,
> name,
> description
> )
> VALUES ($1, $2, $3, $4, $5);
>
> RETURN NULL;
> END;
>
> ' LANGUAGE plpgsql;
>
>
> -- THE TRIGGER
> CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH
> ROW EXECUTE PROCEDURE audit_task();
>
> So the question is, how do I access the row from the original table so I can
> perform the insert?
The old row is OLD and the new row NEW and do not need to be declared as
arguments (in fact trigger functions are always currently created without
declared arguments). I think section 36.10 in the 8.1 docs has info for
other implicit arguments to plpgsql trigger functions.
