Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

From: Ken Denny <ken_at_kendenny.com>
Date: 4 Apr 2005 09:14:17 -0700
Message-ID: <1112631257.730137.42420@g14g2000cwa.googlegroups.com>


DA Morgan wrote:
> whitegoose_at_inorbit.com wrote:
>
> > Unfortunatley this is not a single user database. However, if
possible,
> > could you answer my questions as if it were a single user database?
>
> In a single user database the count of rows will be static as no one
> else is doing any inserts or deletes. Thus a SELECT COUNT(*) as part
> of a trigger will return a real number. In a multiuser situation you
> can do a SELECT COUNT(*) not see 50 other DML statements that are yet
> to be committed. And make a bad decision.
>
> I think you have a design problem here. Certainly the possibility of
one.
>
> > This trigger is being written to enforce a business rule as part of
the
> > customisation of an off-the-shelf application. So it is not the
result
> > of poor design but rather, an awkward situation.
>
> COTS, in many cases, should be an obscenity.
>
> > Basically the rule is that I need to ensure there can only be one
> > residential job per address, and the table contains both the job
type
> > and the address details.
>
> So why not a unique constraint?
>
> > That is to say, I need my trigger, on the JOB table, to check to
see if
> > the JOB.JOBTYPE = 'RES' before insert/update. If so I then check to
see
> > whether another row in JOB with a JOBTYPE = 'RES' has the same
address
> > (STREETNAME, STREETNO, STREETTYPE, CITY). If so, I
> > RAISE_APPLICATION_ERROR.
>
> Ok ... so why not a unique constraint on two columns?
>
> > So I need to be able to select from JOB to compare the values of
the
> > current row to the values of the other rows.
>
> Ok ... so why not a unique constraint on two columns?
>
> > FYI. I am trying to write code that will work for 8i but the client
> > will soon be upgrading to a version that is unknown at this stage.
I
> > would not expect it to be 10g
>
> Ok ... so why not a unique constraint on two columns?
>
> > Thanks,
> > Josh.
>
> Perhaps a unique constraint won't do it but why is not apparent from
> anything you've written.

It only needs to be unique when jobtype = 'RES'. Other jobtypes are allowed to have duplicates. That's why I suggested a sequence column in addition to the unique constraint. Received on Mon Apr 04 2005 - 11:14:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US