Re: Triggers and User Defined Trigger Functions

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