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
