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

On Apr 9, 3:43 pm, "Dean Tomasevic" <dean.tomase...@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

If I'm reading your question correctly, you need to google for:

oracle mutating triggers

If I'm reading your question incorrectly, you just need to have the
trigger on the demo_orders table, and let us know the exact error,
version, trigger code, etc.

jg
--
@home.com is bogus.
http://headrush.typepad.com/creating..._build_a_.html

On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase...@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

On 10 Apr., 06:45, "Vladimir M. Zakharychev"
<vladimir.zakharyc...@gmail.com> wrote:
> On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase...@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.

On Apr 10, 7:25 am, "Dean Tomasevic" <dean.tomase...@googlemail.com>
wrote:
> On 10 Apr., 06:45, "Vladimir M. Zakharychev"
>
>
>
>
>
> <vladimir.zakharyc...@gmail.com> wrote:
> > On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase...@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.

On 10 Apr., 15:26, "EdStevens" <quetico_...@yahoo.com> wrote:
> On Apr 10, 7:25 am, "Dean Tomasevic" <dean.tomase...@googlemail.com>
> wrote:
>
> > On 10 Apr., 06:45, "Vladimir M. Zakharychev"
>
> > <vladimir.zakharyc...@gmail.com> wrote:
> > > On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase...@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

On Apr 10, 8:54 am, "Dean Tomasevic" <dean.tomase...@googlemail.com>
wrote:
> On 10 Apr., 15:26, "EdStevens" <quetico_...@yahoo.com> wrote:
>
>
>
>
>
> > On Apr 10, 7:25 am, "Dean Tomasevic" <dean.tomase...@googlemail.com>
> > wrote:
>
> > > On 10 Apr., 06:45, "Vladimir M. Zakharychev"
>
> > > <vladimir.zakharyc...@gmail.com> wrote:
> > > > On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase...@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.

On 10 Apr., 17:56, "EdStevens" <quetico_...@yahoo.com> wrote:
> On Apr 10, 8:54 am, "Dean Tomasevic" <dean.tomase...@googlemail.com>
> wrote:
>
>
>
> > On 10 Apr., 15:26, "EdStevens" <quetico_...@yahoo.com> wrote:
>
> > > On Apr 10, 7:25 am, "Dean Tomasevic" <dean.tomase...@googlemail.com>
> > > wrote:
>
> > > > On 10 Apr., 06:45, "Vladimir M. Zakharychev"
>
> > > > <vladimir.zakharyc...@gmail.com> wrote:
> > > > > On Apr 10, 2:43 am, "Dean Tomasevic" <dean.tomase...@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.

Dean Tomasevic wrote:

> 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."

With a trigger? Is this an Oracle class or a skit from Comedy Central?

There is no relationship between the two nor can they be made to
relate.

Verify you have the question correctly.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Apr 11, 2:55 am, DA Morgan <damor...@psoug.org> wrote:
> Dean Tomasevic wrote:
> > 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."
>
> With a trigger? Is this an Oracle class or a skit from Comedy Central?
>
> There is no relationship between the two nor can they be made to
> relate.
>
> Verify you have the question correctly.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Hello, I think I got a clue to what u have said or asked.What U must
do is,probabaly write a trigger on the order table so that whenever
somebody takes a new order,u must in the tirgger that is, insert into
a new table.But one more thing ,in case u query the orders table
again, u will get a error that oracle table is mutating,since u cannot
write a trigger on insert that will refer the same table again.