ORACLE TRIGGER WITH JOIN-CodePudding

I want to create something like an audit table.

And I want to insert to this table new values from table CLIENT with join this new values with values from table CODEWORD. I try to use trigger on insert to my table CLIENT:

TRIGGER CLIENT_CODEWORD_INSERT
AFTER 
    INSERT ON TEST2.CLIENT 
    FOR EACH ROW
DECLARE code_word varchar2(100);
BEGIN
    SELECT t2.VAL INTO code_word FROM test2.CODEWORD t2 LEFT JOIN test2.CLIENT t1 ON T2.CLIENTID = t1.ID WHERE t1.Id = :NEW.ID;
    INSERT INTO CLIENT_UPDATE (
    ID
    ,NAME
    ,PHONE
    ,CODEWORD
    )
    VALUES (
    :NEW.ID
    ,SUBSTR (:NEW.NAME, 2,7)
    ,CONCat(:NEW.PHONE,'!')
    , code_word
    );
END;

But I received an error on attempts to insert values into table CLIENT:

SQL Error [4091] [42000]: ORA-04091: table TEST2.CLIENT is mutating, , trigger/function may not see it
ORA-06512: on "TEST2.CLIENT_CODEWORD_INSERT", line 3

CodePudding user response：

No need for join in SELECT statement; just use :new.id.

CREATE OR REPLACE TRIGGER client_codeword_insert
   AFTER INSERT
   ON test2.client
   FOR EACH ROW
DECLARE
   code_word  VARCHAR2 (100);
BEGIN
   SELECT t2.val
     INTO code_word
     FROM test2.codeword t2
    WHERE t2.clientid = :new.id;

   INSERT INTO client_update (id,
                              name,
                              phone,
                              codeword)
        VALUES (:new.id,
                SUBSTR (:new.name, 2, 7),
                CONCAT (:new.phone, '!'),
                code_word);
END;