Re: Create a Trigger for an existing View

On 10 Apr., 06:45, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase..._at_googlemail.com>
> wrote:
>
>
>
> > Hi!
>
> > I created a view from the two existing tables (DEMO_USERS and
> > DEMO_ORDERS).
>
> > Code:
> > CREATE VIEW DEMO_MY_TEST AS
> > SELECT DEMO_USERS.USER_ID, DEMO_ORDERS.ORDER_ID
> > FROM DEMO_USERS
> > INNER JOIN DEMO_ORDERS
> > ON DEMO_ORDERS.USER_ID = DEMO_USERS.USER_ID
>
> > So now my job is to update my view if anybody will take an new order.
> > As example:
> > If user X with the USER_ID 2 will complete a new order my trigger must
> > updating the view.
>
> > But i have problems with the trigger, i know that i must write an
> > updating trigger, but the actuator for the trigger is an "insert into"
> > into the DEMO_ORDERS table, because only if a new order will insert
> > into the DEMO_ORDERS table, then the trigger must be activated and
> > updating the view.
>
> > Thanks a lot.
>
> > Dean Tomasevic
>
> I may be reading it wrong, but there's no need to update the view - it
> will pick up changes from the underlying tables automatically. A view
> is just a stored query, it doesn't immediately materialize and store
> the result set until "updated"; every time you query the view, the
> view statement will be re-executed against current data. So when
> someone adds an order, its ORDER_ID will be returned when you SELECT *
> FROM DEMO_MY_TEST without any extra coding effort.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

hi.

You're right, the view will auto updating if i klick on the view. but what is if i create a new table, and insert the result of "CREATE VIEW DEMO_MY_TEST AS
SELECT DEMO_USERS.USER_ID, DEMO_ORDERS.ORDER_ID FROM DEMO_USERS
INNER JOIN DEMO_ORDERS
ON DEMO_ORDERS.USER_ID = DEMO_USERS.USER_ID" into my new table.
Then i need a trigger which auto statrts if the DEMO_ORDERS table has a new entry, because then
I must insert the new entry of DEMO_ORDERS over a trigger into my new table.

i hope you understand what i mean ;)

Thanks
Dean T. Received on Tue Apr 10 2007 - 07:25:45 CDT