Re: Triggers and User Defined Trigger Functions

Gordan Bobic <gordan@xxxxxxxxxx> · Wed, 09 Mar 2005 14:41:39 +0000

Richard Huxton wrote:
Gordan Bobic wrote:

Hi,

I'm trying to figure out how to do this from the documentation, but I 
can't figure it out. :-(

Here is what I'm trying to do:

CREATE TABLE MyTable
(
    ID    bigserial unique,
    MyData    char(255),
    PRIMARY KEY (ID)
);

CREATE TABLE Archive_MyTable
(
    ID    bigserial unique,
    MyData    char(255),
    PRIMARY KEY (ID)
);

CREATE FUNCTION MyTable_Trigger_DELETE()
RETURNS ???opaque/trigger/HeapTuple??? AS '

RETURNS TRIGGER

You can't use SQL as the target language, it has to be one of the 
procedural languages (e.g. plpgsql)

Something like:

CREATE FUNCTION my_trig_fn() RETURNS trigger AS '
BEGIN
  INSERT INTO archive_mytable (id,mydata) VALUES (OLD.id, OLD.mydata);
  RETURN OLD;
END;
' LANGUAGE plpgsql;

Thanks. :-)

I did that, and I can now create the function and the trigger OK. But 
when the trigger fires (i.e. on DELETE), I get the following error:

DELETE FROM Temp1 WHERE Test = 'test3';
ERROR:  syntax error at or near "$2" at character 44
QUERY:  INSERT INTO Temp2 (ID, test) VALUES ( $1   $2 )
CONTEXT:  PL/pgSQL function "temp1_trigger_delete" line 2 at SQL statement
LINE 1: INSERT INTO Temp2 (ID, test) VALUES ( $1   $2 )

What did I miss?

Gordan

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster