Re: Create a Trigger for an existing View

On 10 Apr., 17:56, "EdStevens" <quetico_..._at_yahoo.com> wrote:
> On Apr 10, 8:54 am, "Dean Tomasevic" <dean.tomase..._at_googlemail.com>
> wrote:
>
>
>
> > 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
>
> Always a good place to start ....
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> Still sounds like a FUBAR design. Perhaps you are mis-understanding
> the nature of this homework assignment. Can you post the task that
> you 'even read'? Not your interpretation. The text as it was
> presented to you.
>
> BTW, we won't do your homework for you, but we will try to get you
> back on track.

The Task:
"Please write a inner join for the tables DEMO_USERS and DEMO_ORDERS. The required information are the USER_ID and ORDER_ID. If anybody take a new order you must be able to see it in your result, but
don't use a view!
Try it with a inner join and a trigger."

Thats the task, and i think one result can be that i create a new table,
write a inner join (look at my first post) and put the result into my table.
And if anybody take a new order my trigger will put the new order into my new table.

Did i understand it correctly?

Thanks.

Dean T. Received on Tue Apr 10 2007 - 16:47:13 CDT