Re: Triggers with arguments
От | Robert B. Easter |
---|---|
Тема | Re: Triggers with arguments |
Дата | |
Msg-id | 0007122151080F.01354@comptechnews обсуждение исходный текст |
Ответ на | Triggers with arguments (Scott Holmes <sholmes@pacificnet.net>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: