Re: Create a Trigger for an existing View

On Apr 10, 2:47 pm, "Dean Tomasevic" <dean.tomase..._at_googlemail.com> wrote:
> 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?
>

The key to understanding is to figure out Oracle's consistency model and when a transaction starts. In general, Oracle won't show you other peoples orders from transactions that start after your transaction starts. Your task appears to be to get around this. There is a fellow named Tom Kyte who has written books explaining things like how to do this. If you go to asktom.oracle.com and search for mutating trigger or autonomous transactions, you will likely run across several ways of dealing with it.

Of course, we now see your task is in conflict with the thread title.

jg

--
@home.com is bogus.
http://www.google.com/tisp/notfound.html