Can I use this instead ?
Niraj Bhaskarbhai Jani, August 31, 2018 - 6:12 pm UTC
If I use this instead of old trigger.
Do you think it will work ?
CREATE OR REPLACE TRIGGER EMAIL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON CUST_ADMIN.EMAIL_T
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_ID NUMBER;
V_ENTITY_ID VARCHAR2(20);
V_ENTITY_TYPE_CD VARCHAR2(5);
BEGIN
SELECT ENTITY_ID INTO V_ENTITY_ID FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;
SELECT ENTITY_TYPE_CD INTO V_ENTITY_TYPE_CD FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;
SELECT MDM_SEQ_NBR.NEXTVAL INTO V_ID FROM DUAL;
IF
:Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT'
THEN
Insert Into ABC
( MDM_IUD_SEQ_NBR
,ENTITY_ID
,ENTITY_TYPE_CD
,DATA_CHANGE_CD
,TABLE_NAME
,PRIMARY_KEY_COL
,SECONDARY_KEY_COL
,PRIMARY_KEY_VAL
,ROW_PROCESS_CD
,ERROR_MSG
,ROW_INSERT_DT
,ROW_UPDATE_DT
,ROW_UPDATER_NAM)
Values ( V_ID
,V_ENTITY_ID
,V_ENTITY_TYPE_CD
,'C'
,'EMAIL_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'EMAIL_MDM_IU_TRIGGER');
END IF;
END;
/
September 04, 2018 - 5:18 am UTC
You can, but if you were planning on rewritting the trigger anyway, you'd look at doing away with the cursor altogether.
Can I use this instead ?
Niraj Bhaskarbhai Jani, August 31, 2018 - 6:13 pm UTC
If I use this instead of old trigger.
Do you think it will work ?
CREATE OR REPLACE TRIGGER EMAIL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON CUST_ADMIN.EMAIL_T
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_ID NUMBER;
V_ENTITY_ID VARCHAR2(20);
V_ENTITY_TYPE_CD VARCHAR2(5);
BEGIN
SELECT ENTITY_ID INTO V_ENTITY_ID FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;
SELECT ENTITY_TYPE_CD INTO V_ENTITY_TYPE_CD FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;
SELECT MDM_SEQ_NBR.NEXTVAL INTO V_ID FROM DUAL;
IF
:Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT'
THEN
Insert Into ABC
( MDM_IUD_SEQ_NBR
,ENTITY_ID
,ENTITY_TYPE_CD
,DATA_CHANGE_CD
,TABLE_NAME
,PRIMARY_KEY_COL
,SECONDARY_KEY_COL
,PRIMARY_KEY_VAL
,ROW_PROCESS_CD
,ERROR_MSG
,ROW_INSERT_DT
,ROW_UPDATE_DT
,ROW_UPDATER_NAM)
Values ( V_ID
,V_ENTITY_ID
,V_ENTITY_TYPE_CD
,'C'
,'EMAIL_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'EMAIL_MDM_IU_TRIGGER');
END IF;
END;
/
turn into single sql and PLSQL Inquiry directives
Rajeshwaran, Jeyabal, September 03, 2018 - 11:47 am UTC
dont need to hard code the program name 'EMAIL_MDM_IU_TRIGGER' like this - instead they can be obtained dynamically with $$PLSQL_UNIT directives & also you can turn the above logic into a simple sql like this.
create or replace trigger email_mdm_iu_trigger
before insert or update on email_t
for each row
begin
insert into abc( mdm_iud_seq_nbr,entity_id,entity_type_cd,data_change_cd
,table_name,primary_key_col,secondary_key_col,primary_key_val
,row_process_cd,error_msg,row_insert_dt,row_update_dt
,row_updater_nam )
select mdm_seq_nbr.nextval, entity_id,entity_type_cd,'C',
'EMAIL_T',null,null,null,
'N',sysdate,null,null,$$plsql_unit
from ENTITY_ADDR_TYPE_LINK_T EATL
where EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR
and :Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT' ;
end;
/
Resolved
Niraj Bhaskarbhai Jani, September 26, 2018 - 4:53 pm UTC
Thank You everyone for helping.
Issue has been resolved.
Issue arises again
NJ, November 27, 2018 - 8:41 pm UTC
Hi,
I modified the trigger a bit which resolved the issue in past.
But Now there's a need again to modify it a bit. I tried to update the trigger but it is not working as expected with the new changes.
Trigger script:
CREATE OR REPLACE TRIGGER schema.EARL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON schema.ENTITY_TABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_ID NUMBER;
BEGIN
SELECT MDM_SEQ_NBR.NEXTVAL INTO V_ID FROM dual;
IF UPDATING OR
:Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )
THEN
Insert Into schema.abc ( MDM_IUD_SEQ_NBR ,ENTITY_ID ,ENTITY_TYPE_CD ,DATA_CHANGE_CD ,TABLE_NAME ,PRIMARY_KEY_COL
,SECONDARY_KEY_COL ,PRIMARY_KEY_VAL ,ROW_PROCESS_CD ,ERROR_MSG ,ROW_INSERT_DT ,ROW_UPDATE_DT ,ROW_UPDATER_NAM)
Values ( V_ID ,:Old.ENTITY_ID ,:Old.ENTITY_TYPE_CD ,'C' ,'ENTITY_TABLE' ,NULL
,NULL ,NULL ,'N' ,NULL ,SYSDATE , SYSDATE ,'EARL_MDM_IU_TRIGGER');
ELSE IF
Inserting OR
:New.Row_Updater_Nam NOT IN ( 'MDM_SERVICE_UPDT','SFDC_SERVICE_UPDT')
THEN
Insert Into schema.abc ( MDM_IUD_SEQ_NBR ,ENTITY_ID ,ENTITY_TYPE_CD ,DATA_CHANGE_CD ,TABLE_NAME ,PRIMARY_KEY_COL
,SECONDARY_KEY_COL ,PRIMARY_KEY_VAL ,ROW_PROCESS_CD ,ERROR_MSG ,ROW_INSERT_DT ,ROW_UPDATE_DT ,ROW_UPDATER_NAM)
Values ( V_ID ,:New.ENTITY_ID ,:New.ENTITY_TYPE_CD ,'C' ,'ENTITY_TABLE' ,NULL
,NULL ,NULL ,'N' ,NULL ,SYSDATE , SYSDATE ,'EARL_MDM_IU_TRIGGER');
END IF;
END IF;
END;
My ultimate goal is to insert/update record,fire trigger, and add record in abc table when row_updater_nam column is not MDM_SERVICE_UPDT and/or SFDC_SERVICE_UPDT. But when i use above script, It is firing the trigger no matter what the value is for ROW_UPDATER_NAM column. I tried:
IF UPDATING OR
:Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT' OR :Old.Row_Updater_Nam != 'SFDC_SERVICE_UPDT'
But same result.
Can anyone help me get my logic correct please?
What I want is, it should not fire trigger when value of row_updater_nam is either MDM_SERVICE_UPDT or SFDC_SERVICE_UPDT.
I mentioned table structure of abc table in the very first message of mine in this conversation.
Here is the table structure for ENTITY_TABLE:
CREATE TABLE schema.ENTITY_TABLE
(
ENTITY_ID VARCHAR2(20 BYTE) NOT NULL,
ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL,
ADDR_ROLE_CD VARCHAR2(10 BYTE) NOT NULL,
ADDR_SEQ_NBR NUMBER(10) NOT NULL,
ADDR_TYPE_CD VARCHAR2(5 BYTE),
EFFEC_DT DATE,
INACTIVE_DT DATE,
ADDR_LINK_INACTIVE_RSN_CD VARCHAR2(5 BYTE),
PRIMARY_ADDR_IND VARCHAR2(1 BYTE),
PRIMARY_IND_LAST_CHG_DT DATE,
THIRD_PARTY_BILLING_IND VARCHAR2(1 BYTE),
ROW_INSERT_DT DATE,
ROW_UPDATE_DT DATE,
ROW_UPDATER_NAM VARCHAR2(50 BYTE);
Here's what I am using for testing :
SELECT *
FROM ENTITY_TABLE
where addr_seq_nbr = '100';
UPDATE ENTITY_TABLE
SET ROW_UPDATER_NAM = 'MDM_SERVICE_UPDT'
WHERE ADDR_SEQ_NBR = '100'
AND ADDR_TYPE_CD = 'POST'
and addr_role_cd = 'PHYSICAL';
SELECT count(*)
FROM schema.abc
where entity_id = '994011355416' ;
Please help me understand what and where I am doing wrong.
Appreciate for your valuable suggestions.
Thank You,
Niraj
November 28, 2018 - 1:07 am UTC
The code:
IF UPDATING OR :Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )
will yield true when
- you are updating
OR
- Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )
So *any* time you update, this comes out as true. Same with the insert.
I suspect you want:
IF UPDATING AND :Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )
To Connor
Niraj Jani, November 28, 2018 - 4:47 pm UTC
I tried using :
IF UPDATING AND :Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )
and still it does the same thing.
But this time it's very random. I mean, if I run update statement with MDM_SERVICE_UPDT/SFDC_SERVICE_UPDT, it sometime fires trigger and sometime not. So I am not sure why it is doing that.
I think my Trigger Script is not that accurate.
Can you Help me understand the issue with that Please ?
Thank You
November 29, 2018 - 12:32 am UTC
We would need a top to bottom test case then, with some real data.
If that's difficult, then simplify your trigger just down to the basic elements of when you want it to fire etc. Add some dbms_output calls and work through some data examples and see where things are not working.