Hi, I know that such questions were often raised, but I still can’t fully understand this topic. Upade table on which there is a trigger

We have 2 tables:

  • Sale_income :

    | item |income |create_user |create_date |last_update_user | update_time| |------|-------|------------|-------------------|-----------------|------------| | 1 | 100 |duck |05-19-2016 |human |05-19-2016 | | 2 | 250 |dog |05-19-2016 |human |05-19-2016 | | 3 | 210 |cat |05-20-2016 |human |05-19-2016 |

  • Sale_income_audit :

     | item |change_id|last_inc|new_inc |user_update|update_date|operation | |------|---------|--------|----------|-----------|-----------|------------| | 1 | 1 |null |05-19-2016|duck |05-19-2016 |I | | 2 | 2 |null |05-19-2016|dog |05-19-2016 |I | | 3 | 33 |null |05-20-2016|cat |05-19-2016 |I |

When someone makes an INSERT , DELETE or UPDATE the Sale_income table, the corresponding field is added to the Sale_income_audit table with the corresponding operation ( operation column). But at the same time, the fields in the main table ( last_update ) should also change. And here we meet the problem of the mutated table.

I decided this as follows: I created a package with three variables and two triggers. In the trigger on the Sale_income update, the time and user data are entered into variables. And after the update in another trigger, the required fields are updated.

Package:

 create or replace package Sale_income_var as v_old_income BINARY_INTEGER := null; v_new_income BINARY_INTEGER := null; v_item BINARY_INTEGER := null; END Sale_income_var;

Triggers:

  1. The first:

     create or replace trigger audit_income_IUD after insert or update or delete on Sale_income for each row begin . . elsif updating then Sale_income_var.v_old_income := :old.income; Sale_income_var.v_new_income := :new.income; if Sale_income_var.v_item is null then Sale_income_var.v_item := :old.item; DBMS_OUTPUT.PUT_LINE(Sale_income_var.v_item); end if; . . end if; end audit_income_IUD;

  2. Second:

     create or replace trigger sale_income_au after update of income on Sale_income begin update Sale_income set last_update_user = user, last_update_date = sysdate where item = Sale_income_var.v_item; INSERT into Sale_income_audit (item, change_id, last_income, new_income, user_update, update_date, operation) VALUES (Sale_income_var.v_item, auto_incr.NEXTVAL, Sale_income_var.v_old_income, Sale_income_var.v_new_income, user, sysdate, 'U'); Sale_income_var.v_item := null; end sale_income_au;

It works, only I feel that this is not the right decision. And this magic with variables is not good either, am I right?

What would you fix here and how would you get out of this situation? Thanks for attention :)

    1 answer 1

    You too artificially created a table mutation situation. It is sometimes necessary to give an Update to the same table on which the trigger itself is written, but this is more likely for code situations when updating a single row of a table it is necessary to change any other rows of the same table.

    If you really need to do this all the same, then you need to create an additional table and in the first trigger (" for each row ") save the necessary information in this table, for example, ID of changing records. In the second trigger, based on this table, make the necessary update and delete the records from the table with the ID.

    The fact is that if the table is given a massive update, i.e. modifying several records at a time, the triggers of " for each row " for each of the records will be called at the beginning, after which the common trigger will be called once. In the package, you save the values ​​of only one record; in the case of a mass update, when the final trigger is processed, you will only have data from the last updated record in the package variables.

    And I propose to solve your problem in a completely different way. You need a BEFORE trigger, in it you can change the values ​​in the records before they are written to disk:

     create or replace trigger audit_income_B_IUD before insert or update or delete on Sale_income for each row begin if inserting or updating then :new.last_update_user := user; :new.last_update_date := sysdate; end if; insert into Sale_income_audit .... end;
    • Thanks, it helped. For some reason, I was convinced that you only need to use after each row, but it turned out to be so simple - Alex Marchuk