After Update Trigger

[Nick G-2]

I have a table with four values (val1, val2, val3, overall_val). I have been trying to create a trigger where after an update is made to either one of the first 3 values, the sum is added and updated into the overall_val field [ for each row ]. I can not separate the fields into a different table and I know this is not the best way to show/store a total field but it is what I have to work with. Currently when I try a basic update after trigger I get the ORA-04091 is a mutating trigger error because the :new.val has not been committed. Does anyone know of a work around to update a row in the same table that fired the trigger? Thanks in advance.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3233
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Mike Chabot]

You might want to specify which database you are working with. You
would not get that error in MS SQL Server.

-Mike Chabot

On Thu, Aug 13, 2009 at 10:49 AM, Nick G<moxiewalk@...> wrote:
>
> I have a table with four values (val1, val2, val3, overall_val). I have been trying to create a trigger where after an update is made to either one of the first 3 values, the sum is added and updated into the overall_val field [ for each row ]. I can not separate the fields into a different table and I know this is not the best way to show/store a total field but it is what I have to work with. Currently when I try a basic update after trigger I get the ORA-04091 is a mutating trigger error because the :new.val has not been committed. Does anyone know of a work around to update a row in the same table that fired the trigger? Thanks in advance.
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3234
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[James Holmes-3]

It's Oracle, is indicated by the ORA in the error number.

mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/


2009/8/14 Mike Chabot <mchabot@...>:
>
> You might want to specify which database you are working with. You
> would not get that error in MS SQL Server.
>
> -Mike Chabot
>
> On Thu, Aug 13, 2009 at 10:49 AM, Nick G<moxiewalk@...> wrote:
>>
>> I have a table with four values (val1, val2, val3, overall_val). I have been trying to create a trigger where after an update is made to either one of the first 3 values, the sum is added and updated into the overall_val field [ for each row ]. I can not separate the fields into a different table and I know this is not the best way to show/store a total field but it is what I have to work with. Currently when I try a basic update after trigger I get the ORA-04091 is a mutating trigger error because the :new.val has not been committed. Does anyone know of a work around to update a row in the same table that fired the trigger? Thanks in advance.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3235
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Nick G-2]

Yes, I am using Oracle 9i. Here is the code for the trigger; which compiles.
The table which fires the trigger is called TEMP_DT.


DECLARE
  VAL4   FLOAT(40);
BEGIN

SELECT SUM(VAL1 + VAL2 + VAL3) INTO VAL4
FROM SCHEMA.TEMP_DT
WHERE LOCATION = :OLD.LOCATION
GROUP BY LOCATION;

UPDATE SCHEMA.TEMP_DT
SET OVERALL_SUM = NEW_VAL4
WHERE LOCATION = :OLD.LOCATION;

END;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3236
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Nick G-2]

Yes, I am using Oracle 9i. Here is the code for the trigger; which compiles.
The table which fires the trigger is called TEMP_DT.


DECLARE
  VAL4   FLOAT(40);
BEGIN

SELECT SUM(VAL1 + VAL2 + VAL3) INTO VAL4
FROM SCHEMA.TEMP_DT
WHERE LOCATION = :OLD.LOCATION
GROUP BY LOCATION;

UPDATE SCHEMA.TEMP_DT
SET OVERALL_SUM = NEW_VAL4
WHERE LOCATION = :OLD.LOCATION;

END;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3237
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Nick G-2]

Well, I found a solution and I figured I'd share just in case someone else comes looking. It turned out I was able to get away from AFTER UPDATE and made it BEFORE UPDATE: the results are exactly what I was looking for. Here is my new Trigger (Before Update).


DELCARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  VAL4   FLOAT(40);
BEGIN
  VAL4 := (:NEW.VAL1 + :NEW.VAL2+ :NEW.VAL3);
  :New.OVERALL_SUM := VAL4;  
END;


One thing to note, I have no idea what PRAGMA AUTONOMOUS_TRANSACTION does...all I know this does what I need it to do.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3238
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Discover Antartica]

Hello All,
Is there a way to backup only tables belonging to a specific schema instead of the entire database in SQL Server 2005 or SQL Server 2008? Also, can I restore only a few tables belonging to a schema instead of restoring all the tables in the database?

Thanks All,
DA



     

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3239
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6