Re: Help!!! Mutating table error !!!!!!

Joe wrote:
>
> We created two tables, table1 and table2, and table1 is a parent table of
> table2.
>
> Table1 has three fileds,
> obj_name, obj_type and total_number
>
> Table2 has three fields
> obj_name, obj_type and obj_values
>
 

>
> How can we write a trigger to update table1 when the total number of
> the obj is changed.
>

Joe,

	My first suggestion would be to eliminate table1 and replace
	it with a view that does the count(*) on the fly.  This is
	really derived data that you're storing in a table, which is
	ok if you can't support deriving the data on the fly.  But think
	about what you are doing every time you delete or insert - you
	have to scan the table and do a count and then update table1.
	The alternative would be an extra scan to derive the data.
	If you eliminate table1, you eliminate the triggers and the
	entire problem goes away.

	Assuming you need table1, I think the problem is that you are
	referencing the same table in the trigger that the trigger is
	firing for.  I can think of one way around it:

	If you can tell in the trigger how many rows were inserted or
	deleted, then you could update table1 and add/subtract that
	number from total_number without referencing table2.  That
	should eliminate the error, assuming that info is available
	in the trigger (haven't used triggers much so I'm not sure).
	You may also have to use a separate insert and delete trigger
	to know whether to insert or subtract.

	Hope this helps.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.