Re: Triggers with arguments

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
> 
> 
> 
>