Home » SQL & PL/SQL » SQL & PL/SQL » TRIGGER
TRIGGER [message #283250] Mon, 26 November 2007 08:16 Go to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

HI all
i have the ff trigger
CREATE OR REPLACE TRIGGER triggertable
  AFTER INSERT OR UPDATE OR DELETE
  ON TBL_PROC_SPEND
  FOR EACH ROW
  DECLARE
  vendorId_variable NUMBER;
  amount_variable NUMBER;
  BEGIN
  SELECT vendor_id, sum(amount_paid)
  into vendorId_variable,amount_variable
  from TBL_PROC_SPEND
  WHERE ROWNUM=1
  group by vendor_id;
  update tbl_vendor
  set TOTAL_PROC_SPEND = amount_variable
  where vendor_id = vendorId_variable;
  END;
/


it is working and has updated the fields according, however when to alter tbl_proc_spend i get a the table is mutating error how do i fix this?


Re: TRIGGER [message #283252 is a reply to message #283250] Mon, 26 November 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did and the error you got.
Basically mutating error is telling you're trying to read/modify in the trigger the table you're trying to modify outside the trigger at the same and this is not allowed as this would lead to data inconsistencies.

Regards
Michel
Re: TRIGGER [message #283637 is a reply to message #283250] Tue, 27 November 2007 16:00 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Your trigger makes absolutly NO sense. Your trigger

CREATE OR REPLACE TRIGGER triggertable
  AFTER INSERT OR UPDATE OR DELETE
  ON TBL_PROC_SPEND
  FOR EACH ROW
  DECLARE
  vendorId_variable NUMBER;
  amount_variable NUMBER;
  BEGIN
  SELECT vendor_id, sum(amount_paid)
  into vendorId_variable,amount_variable
  from TBL_PROC_SPEND
  WHERE ROWNUM=1
  group by vendor_id;
  update tbl_vendor
  set TOTAL_PROC_SPEND = amount_variable
  where vendor_id = vendorId_variable;
  END;
/


tells the database to find a random vendor_id in the same table that is being altered and to add the amount_variable from this random vendor to a total rec in your tbl_vendor. Why are you doing this. If you want to maintain a vendor table, you would write your code like this.

CREATE OR REPLACE TRIGGER triggertable
  BEFORE INSERT OR UPDATE OR DELETE
  ON TBL_PROC_SPEND
  FOR EACH ROW
BEGIN
  if INSERTING THEN
  update tbl_vendor A
  set A.TOTAL_PROC_SPEND = A.TOTAL_PROC_SPEND + :NEW.AMT_PAID
  where A.vendor_id = :NEW.vendor_Id;
  ELSIF UPDATING THEN
  update tbl_vendor A
  set A.TOTAL_PROC_SPEND = A.TOTAL_PROC_SPEND - :OLD.AMT_PAID + :NEW.AMT_PAID
  where vendor_id = :NEW.vendor_Id;
  ELSIF DELETING THEN
  update tbl_vendor A
  set A.TOTAL_PROC_SPEND = A.TOTAL_PROC_SPEND - :OLD.AMT_PAID
  where vendor_id = :OLD.vendor_Id;
  END IF;
END;
/

[Updated on: Tue, 27 November 2007 16:01]

Report message to a moderator

Re: TRIGGER [message #283702 is a reply to message #283250] Wed, 28 November 2007 00:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Bill,
although your code is definitelly better (as it is deterministic), but I am afraid it will end with mutating error everytime as it always tries to update the row which invoked the trigger.
I would tend to use the suggestions described in CREATE A TRIGGER thread.
Re: TRIGGER [message #283927 is a reply to message #283250] Wed, 28 November 2007 07:22 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Flyboy, you are incorrect. The trigger is on the TBL_PROC_SPEND table and the table being updated is TBL_VENDOR. There will be no mutating trigger.
Re: TRIGGER [message #283935 is a reply to message #283927] Wed, 28 November 2007 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What if there is a trigger on tbl_vendor that uses TBL_PROC_SPEND?

Regards
Michel
Re: TRIGGER [message #283939 is a reply to message #283250] Wed, 28 November 2007 07:35 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Laughing

Anything is possible, I love circular logic, it makes me dizzy.
Re: TRIGGER [message #283942 is a reply to message #283927] Wed, 28 November 2007 07:40 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
The trigger is on the TBL_PROC_SPEND table, but within your trigger you access - via SELECT - the very same table.
If I'm not mistaken that is reason enough to get the MUTATING TABLE ERROR
Re: TRIGGER [message #283947 is a reply to message #283942] Wed, 28 November 2007 07:44 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
MarcS wrote on Wed, 28 November 2007 07:40

The trigger is on the TBL_PROC_SPEND table, but within your trigger you access - via SELECT - the very same table.
If I'm not mistaken that is reason enough to get the MUTATING TABLE ERROR

The first block of code is the original invalid trigger, the second block of code is a suggested version. The second block contains no selects and will not (unless circular triggers are in place) cause a mutating error.
Re: TRIGGER [message #284085 is a reply to message #283250] Wed, 28 November 2007 16:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I like instead of triggers...
Re: TRIGGER [message #284101 is a reply to message #283927] Wed, 28 November 2007 21:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Bill,
you are right. It seems I should buy better eyeglasses.
Re: TRIGGER [message #284105 is a reply to message #284085] Wed, 28 November 2007 22:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Why suggesting instread of trigger when there is noi issue with the trigger suggestion of BillB ? It will work perfectly.

Only suggestion from my side is to insert into tbl_vendor for new vendor id if the vendor id is actually New ; Update will not affect any rows in such case.(for Inserting and updating ). But this is purely depending the application. But for Stable and consistent application , this is not needed assumimg that TBL_VENDOR is the master table and TBL_PROC_SPEND wont allow any new Vedor othger that those present in TBL_VENDOR . Smile

Thumbs Up
Rajuvan.
Re: TRIGGER [message #284368 is a reply to message #283250] Thu, 29 November 2007 08:10 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You are correct, not needed.

Still, thought it was helpful to give alternatives in any case. Instead of triggers fit the bill for most trigger needs and cannot suffer from mutating table issues.

They have there own problems I suppose. There are certain advanced features of oracle that will not play in the same sandbox as instead of trigger. But if someone is having real difficulty with mutating and constraining table issues, and is writing lots of the old style workaround code to circumvent it, or disabling foreing keys to make it go away, then instead of triggers is something they should at least be aware of and consider.

Thanks, Kevin
Previous Topic: removing a column
Next Topic: Stored procedure to convert long raw to blob > 32k
Goto Forum:
  


Current Time: Sat Dec 03 20:13:29 CST 2016

Total time taken to generate the page: 0.09048 seconds