Re: Create a Trigger for an existing View

On Apr 10, 7:25 am, "Dean Tomasevic" <dean.tomase..._at_googlemail.com> wrote:
> 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,

Why are you creating a new table?

>and insert the result of
> "CREATE VIEW DEMO_MY_TEST AS
<snip>

> into my new table.

The result of CREATE VIEW is that you get a view - a predefined query - created. Nothing more. Has nothing to do with the absence or existence of any rows on any table. CREATE VIEW does not produce any result to insert into a 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.

Sounds like you are createing a whole new table every time a row is insterted into DEMO_ORDERS?
>
> i hope you understand what i mean ;)

No, we don't. Perhaps if you explained the business problem you are trying to solve instead of the technique you think you need to use to solve it. Right now your design direction sounds like one big FUBAR. Received on Tue Apr 10 2007 - 08:26:51 CDT