Oracle masking data using a trigger-CodePudding

Im trying to create a process that masks data. When I create the trigger I'm getting the error.

ORA-04072: invalid trigger type

I'm unsure why and was hoping someone can explain what the problem is and how to fix it.

The end result is when a user queries cards they should see the masked data and WHEN they query CARDS_TBL they should see all the data (unmasked)


Original implementation 

  CREATE TABLE CARDS (
    CARD_ID NUMBER
    GENERATED BY DEFAULT AS IDENTITY,
  CARD_STR VARCHAR2(16) NOT NULL,
  PRIMARY KEY (CARD_ID)
);

INSERT INTO CARDS(CARD_STR) VALUES('4024007187788590');
INSERT INTO CARDS(CARD_STR) VALUES('5432223398564536');
INSERT INTO CARDS(CARD_STR) VALUES('5430445512530934');
INSERT INTO CARDS(CARD_STR) VALUES('4020156755227854');
INSERT INTO CARDS(CARD_STR) VALUES('5431248766892318');

CREATE OR REPLACE VIEW CARDS_V AS
  SELECT
    CARD_ID,
    REGEXP_REPLACE(CARD_STR, '(^\d{3})(.*)(\d{4}$)', '\1**********\3') AS CARD_STR
  FROM CARDS;

CREATE OR REPLACE TRIGGER CARDS_TRG_INSERT INSTEAD OF
  INSERT ON CARDS_V
  FOR EACH ROW
BEGIN
  INSERT INTO CARDS (CARD_STR) VALUES (:NEW.CARD_STR);
END;

INSERT INTO CARDS_V (CARD_STR) VALUES ('4011589733550908');

CREATE OR REPLACE TRIGGER CARDS_TRG_UPDATE INSTEAD OF
  UPDATE ON CARDS_V
  FOR EACH ROW
BEGIN
  UPDATE CARDS
  SET CARD_STR = :NEW.CARD_STR
  WHERE CARD_ID = :OLD.CARD_ID;
END;



CREATE TABLE CARDS_TBL (
  CARD_ID NUMBER
    GENERATED BY DEFAULT AS IDENTITY,
  CARD_STR VARCHAR2(16) NOT NULL,
  PRIMARY KEY (CARD_ID)
);

INSERT INTO CARDS_TBL(CARD_STR) VALUES('4024007187788590');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5432223398564536');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5430445512530934');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('4020156755227854');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5431248766892318');


CREATE OR REPLACE VIEW CARDS AS
  SELECT
    CARD_ID,
    REGEXP_REPLACE(CARD_STR, '(^\d{3})(.*)(\d{4}$)', '\1**********\3') AS CARD_STR
  FROM CARDS_TBL;

CREATE OR REPLACE TRIGGER CARDS_TBL_TRG_UPDATE  BEFORE UPDATE ON CARDS_TBL 
FOR EACH ROW
BEGIN
  UPDATE CARDS_TBL
  SET CARD_STR = :NEW.CARD_STR
  WHERE CARD_ID = :OLD.CARD_ID;
END;
/

CREATE OR REPLACE TRIGGER CARDS_TBL_TRG_INSERT  BEFORE INSERT ON CARDS 
 FOR EACH ROW
BEGIN
  INSERT INTO CARDS_TBL (CARD_STR) VALUES (:NEW.CARD_STR);
END;


INSERT INTO CARDS_TBL (CARD_STR) VALUES ('2222333344445555');

SELECT * FROM CARDS_TBL;

UPDATE CARDS_TBL 
  SET CARD_STR = '2222333344445566'
  WHERE CARD_ID = 6;
/

SELECT * FROM CARDS;

CodePudding user response：

In this particular example, at least, the trigger CARDS_TBL_TRG_UPDATE doesn't do anything (except raise a MUTATING TABLE exception) and can be dispensed with. Get rid of it and your example runs as expected. See this db<>fiddle