Trigger in oracle view for creating specific increment id

1
votes

I have a table like this T_STG_DWH_READER_MERO

CREATE TABLE "T_STG_DWH_READER_MERO" 
   (    "MESSAGE_ID" VARCHAR2(20 CHAR), 
    "TEXT_PHONE_NUMBER" VARCHAR2(20 CHAR), 
    "TEXT_EMAIL_ADDRESS" VARCHAR2(100 CHAR), 
    "EXPIRED_DTIME" TIMESTAMP (6), 
    "MESSAGE_CODE" VARCHAR2(50 CHAR), 
    "MESSAGE_OWNER" VARCHAR2(20 CHAR), 
    "MESSAGE_TEMPLATE_TYPE" VARCHAR2(100 CHAR), 
    "MESSAGE_ATTRIBUTE" VARCHAR2(500 CHAR), 
    "MESSAGE_FLAG" VARCHAR2(10 CHAR), 
    "MESSAGE_FLAG_DESCRIPTION" VARCHAR2(100 CHAR), 
    "MESSAGE_WORKFLOW_ID" VARCHAR2(100 CHAR), 
    "CREATED_DTIME" TIMESTAMP (6), 
    "PROCESSED_DTIME" TIMESTAMP (6), 
    "CREATED_BY" VARCHAR2(100 CHAR), 
    "MSR_DYNAMIC_BULK_ID" VARCHAR2(50 CHAR), 
    "PARTNER_ID" VARCHAR2(50 CHAR), 
    "ADDITIONAL_DATA" VARCHAR2(255 CHAR)
   );
CREATE SEQUENCE SEQ_MERO3 INCREMENT BY 1 MAXVALUE 99999999 MINVALUE 1 CYCLE CACHE 99999998;

My plan is to have a table view for every department with a different auto increment value message_id from a sequence. Our user will insert into view instead of directly insert into a table. For example :

  1. stgv_mero_tippers with auto-increment message_id from SELECT 'TIP-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVALFROM DUAL;
  2. stgv_mero_mss with auto-increment message_id from SELECT 'MSS-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVALFROM DUAL;
  3. stgv_mero_mitra with auto-increment message_id from SELECT 'MTR-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVALFROM DUAL;

my question is is it possible to create something similar "before insert trigger" on top in oracle views? or is there any way to create a mechanism like my explanation above?

2
oracleplsql
you don't insert into views. You will be inserting to the underlying table. I would to a before insert as you say, create the value and prepend like 'tippers-||SEQ_MERO3.netxval or similar construct. – OldProgrammer

2 Answers

0
votes

Yes, this is possible. I think what you're looking for is an INSTEAD OF trigger. That gets created on a view and handles inserting into the base table(s). You can put additional processing into the trigger body, like

:NEW.message_id := 'TIP-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVAL

See here: https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm#TDDDG52800

0
votes

I give up using trigger on view, I just directly create trigger on top table like below :

CREATE OR REPLACE TRIGGER MSID_TIPPERS 
BEFORE INSERT ON t_stg_dwh_reader_mero 
FOR EACH ROW
BEGIN
  IF :new.partner_id = 'TIPPERS' THEN 
  :NEW.message_id := 'TIP-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVAL;
  ELSIF :new.partner_id = 'MITRA' THEN  
  :NEW.message_id := 'MTR-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVAL;
ELSIF :new.partner_id = 'MSS' THEN  
:NEW.message_id := 'MSS-'||TO_CHAR(SYSDATE,'YYYYMMDD')||'-'||APP_BICC.SEQ_MERO3.NEXTVAL;
  END IF;
END;