Search code examples
sqloracleoracle-sqldeveloperplsqldeveloper

How to automatically add a new record to the second table if a record was added in the first table?


There is a first table M_TAX_ORG and when inserting new record, the second table M_TAX_ORG_ADM_INFO should also add record. (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?

Link to db: db_trigger

enter image description here

enter image description here

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

CREATE OR REPLACE TRIGGER M_TAX_ORG_AFTER_INSERT
    AFTER INSERT
       ON M_TAX_ORG
    FOR EACH ROW
DECLARE
    v_M_TAX_ORG_ADM_INFO_id NUMBER;
BEGIN
    SELECT max(M_TAX_ORG_ADM_INFO_ID) + 1 INTO v_M_TAX_ORG_ADM_INFO_id FROM M_TAX_ORG_ADM_INFO;
    INSERT INTO M_TAX_ORG_ADM_INFO
        ( M_TAX_ORG_ADM_INFO_ID,
          RNN,
          IIN_BIN,
          NAME_R,
          NAME_K,
          M_TAX_ORG_ID,
          IS_MAIN,
          IS_ACTIVE,
          EXTERNAL_ID )
    VALUES
        ( v_M_TAX_ORG_ADM_INFO_id,
          :new.RNN,
          :new.IIN_BIN,
          :new.NAME_R,
          :new.NAME_K,
          :new.M_TAX_ORG_ID,
          :new.IS_MAIN,
          :new.IS_ACTIVE,
          :new.EXTERNAL_ID
        );
end;

Solution

  • This trigger is correct

    CREATE OR REPLACE TRIGGER M_TAX_ORG_AFTER_INSERT
        AFTER INSERT
           ON M_TAX_ORG
        FOR EACH ROW
    BEGIN
        INSERT INTO M_TAX_ORG_ADM_INFO
            ( M_TAX_ORG_ADM_INFO_ID,
              RNN,
              IIN_BIN,
              NAME_R,
              NAME_K,
              M_TAX_ORG_ID,
              IS_MAIN,
              IS_ACTIVE,
              EXTERNAL_ID )
        VALUES
            ( SEQ_M_TAX_ORG_ADM_INFO.nextval,
              :new.RNN,
              :new.IIN_BIN,
              :new.NAME_R,
              :new.NAME_K,
              :new.M_TAX_ORG_ID,
              1,
              :new.IS_ACTIVE,
              (round(DBMS_RANDOM.VALUE() * 999999999999) + 1)
            );
    END;