How to automatically update a column of the second table if the same column is updated in the first-CodePudding

There is a first table M_TAX_ORG and when updating the NAME_R column, the second table M_TAX_ORG_ADM_INFO should also change the NAME_R column automatically. (As you can see from the screenshots, M_TAX_ORG_ADM_INFO is associated with M_TAX_ORG ) Are there ready-made scripts? Maybe with trigger?

CREATE OR REPLACE TRIGGER M_TAX_ORG_AFTER_UPDATE_1
    AFTER UPDATE
    ON M_TAX_ORG
    FOR EACH ROW
DECLARE
    v_NAME_R VARCHAR2(600 char);
BEGIN

    SELECT NAME_R
    INTO v_NAME_R
    FROM M_TAX_ORG WHERE TEST_USER.M_TAX_ORG.M_TAX_ORG_ID = TEST_USER.M_TAX_ORG_ADM_INFO.M_TAX_ORG_ID;

    UPDATE M_TAX_ORG_ADM_INFO ADM
    SET (ADM.NAME_R) = v_NAME_R
    WHERE EXISTS(SELECT M_TAX_ORG.NAME_R
                 FROM M_TAX_ORG
                 WHERE M_TAX_ORG.M_TAX_ORG_ID = ADM.M_TAX_ORG_ID);
END;

I tried this script. But it's not correct.

CodePudding user response：

Without your tables and data, and the behaviour of your current attempt it's hard to be sure; but you seem to be looking for something like:

CREATE OR REPLACE TRIGGER M_TAX_ORG_AFTER_UPDATE_1
    -- only fire if NAME_R was set in the update statement
    AFTER UPDATE OF NAME_R
    ON M_TAX_ORG
    FOR EACH ROW
BEGIN
    UPDATE M_TAX_ORG_ADM_INFO ADM
    SET ADM.NAME_R = :new.NAME_R
    WHERE ADM.M_TAX_ORG_ID = :new.M_TAX_ORG_ID;
END;

db<>fiddle

This uses the new pseudorecord to get the ID and name for the affected row, rather than trying to requery it; and uses those for the update of the other table.

I've also changed it to AFTER UPDATE OF NAME_R so it only fires if that column is affected. But note this only means it was included in update - set NAME_R = <something> - not that the value actually changed. You might want a condition on your update to check if the new value is actually different.

how to make a script for NAME_R and NAME_K?

Add both to the OF clause, and set both values:

CREATE OR REPLACE TRIGGER M_TAX_ORG_AFTER_UPDATE_1
    -- only fire if NAME_R or NAME_K were set in the update statement
    AFTER UPDATE OF NAME_R, NAME_K
    ON M_TAX_ORG
    FOR EACH ROW
BEGIN
    UPDATE M_TAX_ORG_ADM_INFO ADM
    SET ADM.NAME_R = :new.NAME_R, ADM.NAME_K = :new.NAME_K
    WHERE ADM.M_TAX_ORG_ID = :new.M_TAX_ORG_ID;
END;

Having the same data duplicated in multiple tables goes against the principles of normalisation. From the images it looks like M_TAX_ORG_ID should be a primary key in M_TAX_ORG and a foreign key in M_TAX_ORG_ADM_INFO - which may already be the case - and NAME_R and NAME_K should only be in M_TAX_ORG. You then don't need to cascade an update like this, and can just join the tables to get the name values when needed.