Re: Triggers and User Defined Trigger Functions

Richard Huxton <dev@xxxxxxxxxxxx> · Wed, 09 Mar 2005 12:25:56 +0000

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

INSERT INTO Archive_MyTable
(
    ID,
    MyData
)
VALUES
(
    OLD.ID,
    OLD.MyData
);
RETURN OLD;
' LANGUAGE SQL;

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;

You can also use many other languages for functions - tcl/perl/python (I 
think)/java etc. Check your language of choice supports triggers though.

--
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
     message can get through to the mailing list cleanly