CODEWITHSUNDEEP
sqlitetriggers
nightcrawler
nightcrawler

Reputation: 327

SQL Too many levels of trigger recursion

@ http://www.mediafire.com/download/5h674s7wdzk8tek/m4_2013.db I am using sqlitestudiov3.01 for the above databse. The problem is that it wont let edit data values & initiates an error

An error occurred while commiting the data: too many levels of trigger recursion

This error get resolved if I drop my trigger. My triiger is as follows

CREATE TRIGGER MACRO_A
       AFTER UPDATE ON ELZ_A
       FOR EACH ROW
BEGIN
    UPDATE ELZ_A
       SET CURRENT_DENSITY = ROUND( ( LOAD / 2.721 ) , 2 );

    UPDATE ELZ_A
       SET VOLTS_AVG = ROUND( ( VOLTS_T / ELEMENTS ) , 2 );

    UPDATE ELZ_A
       SET VOLTS_STNDR = ROUND( 2.4 +( ( 12.75 / 2.721 ) *( ( VOLTS_AVG - 2.4 ) / CURRENT_DENSITY )  ) -( ( 90 - CATHOLYTE_TEMP ) * 0.01 ) +( ( 32 - CATHOLYTE_CONC ) * 0.02 ) , 2 );

    UPDATE ELZ_A
       SET KF_FACTOR = ROUND( ( ( VOLTS_AVG -( 90 - CATHOLYTE_TEMP ) * 0.016 *( ( LOAD / 2.721 ) / 5 )  ) +( ( 32 - CATHOLYTE_CONC ) * 0.033 *( ( LOAD / 2.721 ) / 5 ) - 2.4 )  ) /( LOAD / 2.721 ) , 3 );

    UPDATE ELZ_A
       SET PRODUCTION = ROUND( 0.001492 * 24 * ELEMENTS * LOAD *( EFFICIENCY / 100 ) , 2 );

    UPDATE ELZ_A
       SET TEMP_CORRELATION = ROUND( 7 *( CURRENT_DENSITY / 3 ) *( 90 - CATHOLYTE_TEMP ) , 2 );

    UPDATE ELZ_A
       SET CONC_CORRELATION = ROUND( 14 *( CURRENT_DENSITY / 3 ) *( 32 - CATHOLYTE_CONC ) , 2 );

    UPDATE ELZ_A
       SET DC_POWER_PER_TON = ROUND( ( ( 24 * LOAD * VOLTS_T ) / PRODUCTION ) - TEMP_CORRELATION - CONC_CORRELATION, 0 );
END;

Well I don't know if above follows standard but atleast in some applications it works flawlessly & mimicks Excel formula calculation. Anyone with ideas

Upvotes: 2

Views: 3645

Answers (1)

CL.
CL.

Reputation: 180161

This trigger fires when any column gets updated, so it fires also for the column that itself updates.

If you have a distinction between "input" and "output" column, where only the latter are computed by trigger, you can restrict the trigger to fire only on the input columns:

CREATE TRIGGER ...
AFTER UPDATE OF LOAD, VOLTS_T, ELEMENTS, ... ON ...

However, it is a bad idea to store derived values in the database. Better use a view to compute the derived values on the fly:

CREATE VIEW ELZ_with_all_values AS
SELECT LOAD,
       VOLTS_T,
       ELEMENTS,
       ...
       LOAD / 2.721 AS CURRENT_DENSITY,
       VOLTS_T / ELEMENTS AS VOLTS_AVG,
       ...
FROM ELZ_A;

Please note that you should never use ROUND() for values that you use for further computations. (In Excel, a format would affect only how a value is displayed.)

Upvotes: 3

Related Questions