Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger
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