Question: Oracle database - determine what table triggered a trigger

Question

Oracle database - determine what table triggered a trigger

Answers 2
Added at 2017-01-05 22:01
Tags
Question

I am working with Oracle Express 12c. One of the tables I created has an associated trigger which prevents it from directly updating one of its columns. But it fires even when another table, which should have this kind of access, tries to do it.

For example:

I have tables A and B, and B has a foreign key that links it to A. I purposefully added one of the attributes from A to B. One trigger, let's call it UPD_FROM_B, prevents B from updating this attribute. Another, UPD_FROM_A, should update this attribute on B if it is updated on A. Now UPD_FROM_B prevents UPD_FROM_A from doing what it is supposed to.


Or through a working example:

There are two tables: customer and order. Customer can have multiple orders, but one order has only one customer. For the sake of the project, I had to put customer_name on the order, even though every order has customer_id as foreign key.

One trigger - UPD_NAME_ORDER prevents order from updating the customer_name, and the other - UPD_NAME_CUST updates this column in the appropriate row of the order table whenever customer_name is updated in customer


How can I determine which table triggered the action and allow UPDATE for one, but still prevent it from the other?

Answers to

Oracle database - determine what table triggered a trigger

nr: #1 dodano: 2017-01-06 01:01

At face value, the way I know to do this is to use a package variable as a gate key and share it between the 2 triggers. Trigger A will set the state variable before its nested update of B. Trigger B will check if A set the var, if so, the update succeeds, if not, then B knows A is not the caller, and it should block the update.

Also, I assume your intention is to implement an "UPDATE CASCADE" trigger to update the child record foreign key values based on the parent update, preserving the relationship while updating the FK value. If so, you have to be careful with this approach, it will only work correctly if you disallow multi-row updates.

First a package and state var:

CREATE PACKAGE IsUpdating IS
  A number;
END;

At top of trigger A do something like below. The exception handler is a "finally" block that always executes to avoid leaving the package variable in an invalid state in case of an error on the update:

CREATE TRIGGER A_UPD_CASCADE after update on A for each row
BEGIN
    IsUpdating.A := 1;
    update B set B.FKID = :new.FKID WHERE B.FKID = :old.FKID;
    IsUpdating.A := 0;

EXCEPTION
    WHEN OTHERS
    THEN
        IsUpdating.A := 0;
        RAISE;
END;

Inside trigger B do this:

CREATE TRIGGER B_UPD_CASCADE before update on B
BEGIN
    if IsUpdating.A != 1 then
        -- Disallow update since it is coming from B alone
        RAISE;
    end if;
END;

The pitfall with CASCADE UPDATE is with multi-row parent updates in a single statement, Oracle will execute the trigger for each parent value, causing some child values to update multiple times based on chained before and after values.

nr: #2 dodano: 2017-01-06 13:01

I think you must change your trigger UPD_FROM_B only. Firstly you select value of column from table A when parent key and foreign key are equal, then compare this value to value of column from table B. If this value equal your trigger allow to do this updating, else not. You write this code as follow:

CREATE TRIGGER UPD_FROM_B before update on B
DECLARE
 val A.upd_column%TYPE; 
BEGIN
    select A.upd_column into val
    where A.ID=B.FKID
    if val=B.upd_column then

        RAISE;
    else ......
    end if;
END;
Source Show
◀ Wstecz