Re: Triggers with arguments - Mailing list pgsql-general
From | Richard Harvey Chapman |
---|---|
Subject | Re: Triggers with arguments |
Date | |
Msg-id | Pine.LNX.4.10.10007121617310.14112-100000@smile.3gfp.com Whole thread Raw |
In response to | Triggers with arguments (Scott Holmes <sholmes@pacificnet.net>) |
Responses |
Re: Triggers with arguments
|
List | pgsql-general |
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 > > > >
pgsql-general by date: