Forum OpenACS Q&A: postgresql triggers

Collapse
1: postgresql triggers
Posted by David Bell on 05/29/03 08:43 AM
Hi
I want to create a trigger in postgresql on insert into a table that calls the notification__new function to add a notification. However I need to pass the arguments from the insert to the notification__new function.
i.e. I want to have something like the followin
CREATE TRIGGER report_alert AFTER INSERT ON mytable FOR EACH ROW EXECUTE PROCEDURE notification__new(myarguments);

where myarguments includes some values out of the original row.

Is there a way to do this?
I havent been able to find it in the postgresql docs.

Thanks alot

David

Collapse
2: Re: postgresql triggers (response to 1)
Posted by Matthew Geddert on 05/29/03 08:50 AM
why are you trying to do this via a postgresql trigger as opposed to using a service contract via the tcl api (which notifications was designed to do)? Take a look at the way forums integrates with notifications - that is how it is "supposed" to be done. If you have a very compelling reason to do this via a trigger then you probably want ot use NEW.field1, NEW.field2 as placeholders in your proc to submit that info to notifications.... do a grep through packages like this for an example... 
/web/service/packages/# grep -R "NEW." * | grep "/postgres"
Collapse
3: Re: postgresql triggers (response to 1)
Posted by David Bell on 05/30/03 01:08 AM
Matthew,
      Thank you for your reply. I am still a novice or worse at OACS, the problem I have is that the records will be inserted into a table by an external application. I then want to send notifications of the records existance and control subscriptions to these notifications through OACS. Can I do this without triggers? I am still trying to fumble my way through using OACS.

Thanks again

David

Collapse
4: Re: postgresql triggers (response to 1)
Posted by Matthew Geddert on 05/30/03 05:02 AM
if by external application you mean something that connects directly to the databasae without the use of aolserver then yes you probably want to do it via a trigger. if you mean via some other app it will probably be possible via plsql - still recommend looking at the way forums integrates with notifications - because this will tell you what needs to be done via plsql...
Collapse
5: Re: postgresql triggers (response to 1)
Posted by Andrei Popov on 05/30/03 11:40 AM
I think you can use OLD.field_name and NEW.field_name in triggers in Postgres.  This is, however, for updates, not inserts.  NEW.filed_name maybe there for inserts as well, but I don't remember for sure.  Check the docs -- it is pretty well written section.
Collapse
6: Re: postgresql triggers (response to 1)
Posted by Matthew Geddert on 05/30/03 11:49 AM
NEW.field_name is available for insert procs...