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
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 |