Re: Create a Trigger for an existing View

On 10 Apr., 15:26, "EdStevens" <quetico_..._at_yahoo.com> wrote:
> 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.

Well i even read my task and my job is to insert the result of my inner join into a new table and updating the new table every time if a new entry will make in the DEMO_ORDERS table (thats a test which i must make for the school..), and this i must realise with a trigger, i think.
Because if a new entry will make in the DEMO_ORDERS table the trigger must be activated and insert the new ORDER entry into my own created table.

Dean Received on Tue Apr 10 2007 - 08:54:23 CDT