Problem trying to load trigger

From: Michael Gould <mgould(at)intermodalsoftwaresolutions(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem trying to load trigger
Date: 2009-08-02 17:08:43
Message-ID: 448781787129c04f9fc8b836e7e0d044@intermodalsoftwaresolutions.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

I'm trying to use a single trigger to do a couple of things...

The first is when a record is created to timestamp the createdatetime and
the createuser columns with the current date/time or user. If there is a
update then I want to make before and after images of the record and if a
delete I want to keep the before image of the record.

when I try and load this I get the following errors.

I'm new to postgres so I'm not sure where I'm off here. Any help is greatly
appreciated

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

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2009-08-02 17:42:06 Re: Problem trying to load trigger
Previous Message Tom Lane 2009-08-02 16:44:55 Re: building a binary-portable database