Thread: Triggers with arguments

I'm afraid I just don't see how this is done without being able to pass
arguments to the procedure or actually running an additional SQL statement
within the trigger:

  I have a "notes" table that is potentially linked to records in many other
tables.  The primary key is created from 1) the name of the table, and 2) the
primary key value of that table.  When one of these records, with notes, is
deleted, I need to make sure that the notes records are deleted as well.

I've been playing around with this and perhaps my code that doesn't work will
illustrate what I need to accomplish and perhaps one of you kind readers will
show me just how to do it.

CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
  DECLARE
    fname alias for $1;
    rkey alias for $2;
  BEGIN
    delete from stxnoted where filename = fname
       and record_key = rkey;
  END;'
LANGUAGE 'plpgsql';


create trigger del_location_trig
after delete
  on location
  for each row
    execute procedure del_stxnoted("location", 'old.wher');

Postgres will not create this trigger as it does not recognize the function
del_stxnoted as actually existing.


I am attempting to convert a large suite of applications that currently run on
an Informix database.  The syntax for this is

create trigger del_location delete on location referencing
    old as pre_del
    for each row
        (
        delete from stxnoted  where ((filename = 'location' ) AND (record_key
    = pre_del.wher ) ) );


Thanks,  Scott

If I understand correctly, you have something like this:

CREATE TABLE rec_* (
    num   integer primary key;
    other ...
);

CREATE TABLE notes (
    name   CHAR(20),
    num    INTEGER,
    note   VARCHAR(200),
           PRIMARY KEY(name, num)
);

So, you have many different tables like rec_*, and one notes table that
can refer to them all.

Perhaps then, you want something like this:

CREATE TABLE notes (
    name   CHAR(20),
    num    INTEGER REFERENCES rec_*
             ON UPDATE CASCADE
             ON DELETE CASCADE,
    note   VARCHAR(200),
           PRIMARY KEY(name, num)
);

'cept I just realized that a column can't reference multiple tables (can
it?).  Anyway, I found the above idea in Bruce's book in Chapter 14,
"Modification of Primary Key Row."

If I'm reading it correctly, deleting the record from rec_*, should cause
the corresponding record in notes to be deleted as well.

perhaps this'll help,

R.




On Wed, 12 Jul 2000, Scott Holmes wrote:

> I'm afraid I just don't see how this is done without being able to pass
> arguments to the procedure or actually running an additional SQL statement
> within the trigger:
>
>   I have a "notes" table that is potentially linked to records in many other
> tables.  The primary key is created from 1) the name of the table, and 2) the
> primary key value of that table.  When one of these records, with notes, is
> deleted, I need to make sure that the notes records are deleted as well.
>
> I've been playing around with this and perhaps my code that doesn't work will
> illustrate what I need to accomplish and perhaps one of you kind readers will
> show me just how to do it.
>
> CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
>   DECLARE
>     fname alias for $1;
>     rkey alias for $2;
>   BEGIN
>     delete from stxnoted where filename = fname
>        and record_key = rkey;
>   END;'
> LANGUAGE 'plpgsql';
>
>
> create trigger del_location_trig
> after delete
>   on location
>   for each row
>     execute procedure del_stxnoted("location", 'old.wher');
>
> Postgres will not create this trigger as it does not recognize the function
> del_stxnoted as actually existing.
>
>
> I am attempting to convert a large suite of applications that currently run on
> an Informix database.  The syntax for this is
>
> create trigger del_location delete on location referencing
>     old as pre_del
>     for each row
>         (
>         delete from stxnoted  where ((filename = 'location' ) AND (record_key
>     = pre_del.wher ) ) );
>
>
> Thanks,  Scott
>
>
>
>

What I'm after are triggers on delete fro several tables calling the same
procedure.  I am not at liberty to change the schema of the database as I need
to accomodate what is actually an ancient system - that goes back before the
days of blobs and large text fields.  In my example, deleting a record from
the "location" table, the trigger needs to delete associated rows from the
notes table.  The notes table is indexed by fields for 1) a table name (in
this case "location", and 2) a record key (the value of the primary field in
the location table).

In postgresql, the trigger execute a procedure that must be defined with no
arguments and returning opaque.  What I don't know how to do is write a
procedure that knows the values for the notes table index key (table name and
primary key value).  Examples in the manual indicate that arguments may indeed
be passed, and it refers to such procedures as general trigger functions.  I,
however, have not been able to figureout how to write such functions.

Thanks, Scott

Scott Holmes  <sholmes@pacificnet.net> writes:
> CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
>   DECLARE
>     fname alias for $1;
>     rkey alias for $2;
>   BEGIN
>     delete from stxnoted where filename = fname
>        and record_key = rkey;
>   END;'
> LANGUAGE 'plpgsql';

> create trigger del_location_trig
> after delete
>   on location
>   for each row
>     execute procedure del_stxnoted("location", 'old.wher');

> Postgres will not create this trigger as it does not recognize the function
> del_stxnoted as actually existing.

Uh, the trigger creates just fine for me.  I think your problem is
with the way you're trying to get at the trigger arguments in the
function body.  There's an example of the right way in the plpgsql
regress test:

create function tg_chkslotname() returns opaque as '
begin
    if substr(new.slotname, 1, 2) != tg_argv[0] then
        raise exception ''slotname must begin with %'', tg_argv[0];
    end if;
    return new;
end;
' language 'plpgsql';

create trigger tg_chkslotname before insert
    on PSlot for each row execute procedure tg_chkslotname('PS');

create trigger tg_chkslotname before insert
    on WSlot for each row execute procedure tg_chkslotname('WS');


The documentation mentions tg_argv[] but doesn't seem to give an example
:-(

            regards, tom lane

On Wed, 12 Jul 2000, Scott Holmes wrote:
> I'm afraid I just don't see how this is done without being able to pass
> arguments to the procedure or actually running an additional SQL statement
> within the trigger:
>
>   I have a "notes" table that is potentially linked to records in many other
> tables.  The primary key is created from 1) the name of the table, and 2) the
> primary key value of that table.  When one of these records, with notes, is
> deleted, I need to make sure that the notes records are deleted as well.
>
> I've been playing around with this and perhaps my code that doesn't work will
> illustrate what I need to accomplish and perhaps one of you kind readers will
> show me just how to do it.
>
> CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
>   DECLARE
>     fname alias for $1;
>     rkey alias for $2;
>   BEGIN
>     delete from stxnoted where filename = fname
>        and record_key = rkey;
>   END;'
> LANGUAGE 'plpgsql';
>
>
> create trigger del_location_trig
> after delete
>   on location
>   for each row
>     execute procedure del_stxnoted("location", 'old.wher');
>
> Postgres will not create this trigger as it does not recognize the function
> del_stxnoted as actually existing.
>
>
> I am attempting to convert a large suite of applications that currently run on
> an Informix database.  The syntax for this is
>
> create trigger del_location delete on location referencing
>     old as pre_del
>     for each row
>         (
>         delete from stxnoted  where ((filename = 'location' ) AND (record_key
>     = pre_del.wher ) ) );
>
>
> Thanks,  Scott

Maybe try it like this:

CREATE FUNCTION del_stxnoted() RETURNS OPAQUE AS '
BEGIN
    DELETE FROM stxnoted WHERE filename = OLD.fname AND record_key = OLD.rkey;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER del_location_trig
    AFTER DELETE ON location FOR EACH ROW
    EXECUTE PROCEDURE del_stxnoted();


        - Robert