Re: INSTEAD OF trigger on VIEWs - Mailing list pgsql-hackers

I have tried using INSTEAD rules but there are some
conditional logic that needs to happen inside the rule
(performing counts, getting and storing the primary
key of the master record etc.).  AFAIK, rules only
allows conditional logic to be check prior to
execution of the rule and not inside the rule itself. 
One way to get around this is to allow calling a
stored procedure inside the rule. This stored
procedure should have full access of NEW.* (and OLD.*
in the case of UPDATE and DELETE).  This way the
manual INSERT, UPDATE or DELETE on the actual tables
can be performed from inside the stored procedure.




--- Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:
> You can probably just create an INSTEAD rule on the
> view...
> 
> Chris
> 
> --= Tono =-- wrote:
> > Is there any plans to create an INSTEAD OF trigger
> on
> > VIEWS?   I have view which consists of a master
> and
> > detail table.  When a row is inserted into the
> view,
> > the view needs to figure out if the master record
> > already exsists. If the record does not exists in
> the
> > master table, then insert into the master and the
> > detail table.  If the record already exists in the
> > master, just insert into detail table. 
> Conversely, if
> > a delete record is performed on the view, the view
> > needs to figure out if it only needs to delete
> from
> > the detail table, or should it also delete from
> the
> > master table when all the detail records are
> already
> > deleted.  In Oracle this is easily done using
> INSTEAD
> > OF triggers.  INSTEAD OF triggers can only be
> created
> > for VIEWs. The purpose of it is to "short-circuit"
> the
> > event (INSERT, UPDATE or DELETE) and perform
> whatever
> > is specified in the trigger.
> > 
> > CREATE OR REPLACE TRIGGER schema.trigger_name
> INSTEAD
> > OF INSERT ON object_name 
> > BEGIN
> > -- Perform the following instead --
> > END;
> > 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam
> protection around 
> > http://mail.yahoo.com 
> > 
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com