Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail
From: whitegoose@inorbit.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger
Date: 4 Apr 2005 01:08:54 -0700
Organization: http://groups.google.com
Lines: 57
Message-ID: <1112602134.610929.20420@f14g2000cwb.googlegroups.com>
References: <aafea0a8.0504031956.438cdf3@posting.google.com>
   <1112595217.815019@yasure>
NNTP-Posting-Host: 211.26.24.154
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1112602138 16665 127.0.0.1 (4 Apr 2005 08:08:58 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 4 Apr 2005 08:08:58 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting-host=211.26.24.154;
   posting-account=Vh11bgwAAAAlVjTq9mME6UchIPKdn5_t
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:117915

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