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: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 04 Apr 2005 08:28:52 -0700
Message-ID: <1112628317.728082@yasure>


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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon Apr 04 2005 - 10:28:52 CDT

Original text of this message

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