Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger

DA Morgan wrote:
> Josh White wrote:
>
> > Thanks in advance - I cannot work this out myself because I don't
have
> > an Oracle database on my machine to test my code until next week -
and
> > I am new to Oracle.
> >
> > Josh.
>
> If you need to query the table on which the query is located most
likely
> you have a design problem: Explore this possibility first.
>
> The question you are assuming you can ask, in your trigger, is not
> easily answered unless you assume you have a single user database.
> What is the actual business case?
>
> To download Oracle go to http://otn.oracle.com.
>
> The rules around mutating triggers changed in Oracle 10g. What
version
> are you coding for?
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

Unfortunatley this is not a single user database. However, if possible, could you answer my questions as if it were a single user database?

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.

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.

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

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

Thanks,
Josh. Received on Mon Apr 04 2005 - 03:08:54 CDT