Re: Help with Mutating Table - Are there other solutions?

From: Joachim Zobel <jzobel_at_my-dejanews.com>
Date: Sat, 13 Dec 2003 23:04:41 +0100
Message-ID: <pan.2003.12.13.22.04.41.392595@my-dejanews.com>

On Sat, 13 Dec 2003 11:39:23 -0500, contrapositive wrote:

> Two tables, ORDERS and DETAIL_LINES:
>
> ORDERS.order_id
> ORDERS.cust_id
> ORDERS.order_status, etc...
>
> DETAIL_LINES.order_id
> DETAIL_LINES.line_id
> DETAIL_LINES.line_status, etc...
>
> An order has one or more detail lines.
>
> We want an update trigger (for each row) on DETAIL_LINES that says if this
> line_status is being changed to HOLD, then all the other lines for this
> order should change to HOLD. Obviously, that means we're attempting to
> update the table that's being triggered, and the "mutating table" error is
> raised.

A view could do.

CREATE VIEW DETAIL_LINES
AS
SELECT
  ...

  NVL(	SELECT MIN('o') 
	FROM DETAIL_LINES_ORG
        WHERE order_id=main.order_id
        AND line_status='o'),
	main.line_status) AS line_status,

  ...
FROM DETAIL_LINES_ORG main

(untested).

Hth,
Joachim

-- 
Warnung: \" kann Augenkrebs verursachen. 

Received on Sat Dec 13 2003 - 16:04:41 CST