Re: Problem trying to load trigger

On 2 Aug 2009, at 19:08, Michael Gould wrote:

> CREATE OR REPLACE FUNCTION iss.accessor_trigger() RETURNS "trigger"
> AS $$
> BEGIN
>
> IF (TG_WHEN = 'BEFORE') THEN
> IF (TG_OP = 'INSERT') THEN
> NEW.createdatetime := current_timestamp
> NEW.createuser := current_user
> ELSIF (TG_OP = 'UPDATE') THEN
> INSERT into iss.auditaccessor SELECT 'B','C',OLD.*;
> ELSIF (TG_OP = 'DELETE') THEN
> INSERT into iss.auditaccessor SELECT 'B','D',OLD.*;
> END IF;
> RETURN NEW;
> ELSIF (TG_WHEN = 'AFTER') THEN
> IF (TG_OP = 'INSERT') THEN
> ELSIF (TG_OP = 'UPDATE') THEN
> INSERT into iss.auditaccessor SELECT 'A','C',NEW.*;
> ELSIF (TG_OP = 'DELETE') THEN
> END IF;
> RETURN OLD;
> END IF;
>
> END $$ LANGUAGE plpgsql VOLATILE;

> ERROR: syntax error at or near "$1"
> LINE 1: SELECT current_timestamp $1 := current_user ELSIF ( $2
> =...
> ^
> QUERY: SELECT current_timestamp $1 := current_user ELSIF ( $2 =
> 'UPDATE') THEN INSERT into iss.auditaccessor SELECT 'B','C', $3 .*
> CONTEXT: SQL statement in PL/PgSQL function "accessor_trigger" near
> line 8
>
> ********** Error **********
>
> ERROR: syntax error at or near "$1"
> SQL state: 42601
> Context: SQL statement in PL/PgSQL function "accessor_trigger" near
> line 8

You're missing a few semi-colons after the first two assignments. I'm
also not sure whether the empty THEN clauses at lines 14 and 17 will
be accepted, it's probably better to leave them out. And lastly, you
don't need braces around your conditional expressions or identifier
quotation around the RETURN-type.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a75cff110131139260432!