How to get the trigger the way logic wants ?
1
I am trying to create trigger which fires and store results in aud table when any new row has been updated or inserted in the source table.
I am using below script to create that trigger:
CREATE OR REPLACE TRIGGER abc.E_M_IU_T
BEFORE INSERT OR UPDATE
ON abc.E_A_R_L_T
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 AND
:Old.Row_Updater_Nam !='M_S_U' OR :Old.Row_Updater_Nam!='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:Old.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
elsif INSERTING AND
:New.Row_Updater_Nam !='M_S_U' OR :New.Row_Updater_Nam !='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:New.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
END IF;
END;
/
I am able to create the trigger successfully. But when I am inserting/updating any records in source table, it writes to aud table regardless of the value of row_updater_nam column. I have used != 'm_s_u' and != 's_s_u' in the trigger script, but it seems like it is ignoring it.
Can anyone help me to get it resolved?
If you feel my trigger script is not accurate, please advise as well.
Thank You
oracle triggers
asked Nov 19 '18 at 22:26
user162139user162139
61
add a comment |
1
I am trying to create trigger which fires and store results in aud table when any new row has been updated or inserted in the source table.
I am using below script to create that trigger:
CREATE OR REPLACE TRIGGER abc.E_M_IU_T
BEFORE INSERT OR UPDATE
ON abc.E_A_R_L_T
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 AND
:Old.Row_Updater_Nam !='M_S_U' OR :Old.Row_Updater_Nam!='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:Old.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
elsif INSERTING AND
:New.Row_Updater_Nam !='M_S_U' OR :New.Row_Updater_Nam !='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:New.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
END IF;
END;
/
I am able to create the trigger successfully. But when I am inserting/updating any records in source table, it writes to aud table regardless of the value of row_updater_nam column. I have used != 'm_s_u' and != 's_s_u' in the trigger script, but it seems like it is ignoring it.
Can anyone help me to get it resolved?
If you feel my trigger script is not accurate, please advise as well.
Thank You
oracle triggers
asked Nov 19 '18 at 22:26
user162139user162139
61
1
Use an AFTER trigger, not a BEFORE.
– Bob Jarvis
Nov 20 '18 at 3:06
add a comment |
1
1
1
I am trying to create trigger which fires and store results in aud table when any new row has been updated or inserted in the source table.
I am using below script to create that trigger:
CREATE OR REPLACE TRIGGER abc.E_M_IU_T
BEFORE INSERT OR UPDATE
ON abc.E_A_R_L_T
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 AND
:Old.Row_Updater_Nam !='M_S_U' OR :Old.Row_Updater_Nam!='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:Old.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
elsif INSERTING AND
:New.Row_Updater_Nam !='M_S_U' OR :New.Row_Updater_Nam !='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:New.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
END IF;
END;
/
I am able to create the trigger successfully. But when I am inserting/updating any records in source table, it writes to aud table regardless of the value of row_updater_nam column. I have used != 'm_s_u' and != 's_s_u' in the trigger script, but it seems like it is ignoring it.
Can anyone help me to get it resolved?
If you feel my trigger script is not accurate, please advise as well.
Thank You
oracle triggers
asked Nov 19 '18 at 22:26
user162139user162139
61
I am trying to create trigger which fires and store results in aud table when any new row has been updated or inserted in the source table.
I am using below script to create that trigger:
CREATE OR REPLACE TRIGGER abc.E_M_IU_T
BEFORE INSERT OR UPDATE
ON abc.E_A_R_L_T
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 AND
:Old.Row_Updater_Nam !='M_S_U' OR :Old.Row_Updater_Nam!='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:Old.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
elsif INSERTING AND
:New.Row_Updater_Nam !='M_S_U' OR :New.Row_Updater_Nam !='S_S_U' then
BEGIN
Insert Into abc.aud
( 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.E_ID
,:New.E_CD
,'C'
,'E_A_R_L_T'
,NULL
,NULL
,NULL
,'N'
,NULL
,SYSDATE
,NULL
,'E_M_IU_T');
END;
END IF;
END;
/
I am able to create the trigger successfully. But when I am inserting/updating any records in source table, it writes to aud table regardless of the value of row_updater_nam column. I have used != 'm_s_u' and != 's_s_u' in the trigger script, but it seems like it is ignoring it.
Can anyone help me to get it resolved?
If you feel my trigger script is not accurate, please advise as well.
Thank You
oracle triggers
oracle triggers
asked Nov 19 '18 at 22:26
user162139user162139
61
asked Nov 19 '18 at 22:26
user162139user162139
61
asked Nov 19 '18 at 22:26
user162139user162139
61
asked Nov 19 '18 at 22:26
user162139user162139
61
asked Nov 19 '18 at 22:26
user162139user162139
61
61
1
Use an AFTER trigger, not a BEFORE.
– Bob Jarvis
Nov 20 '18 at 3:06
add a comment |
1
Use an AFTER trigger, not a BEFORE.
– Bob Jarvis
Nov 20 '18 at 3:06
1
1
Use an AFTER trigger, not a BEFORE.
– Bob Jarvis
Nov 20 '18 at 3:06
Use an AFTER trigger, not a BEFORE.
– Bob Jarvis
Nov 20 '18 at 3:06
add a comment |
1 Answer
1
active
oldest
votes
1
It seems like a simple mistake in the logical operators used. You have
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' OR :Old.Row_Updater_Nam != 'S_S_U' THEN
While you should be using AND instead of OR:
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' AND :Old.Row_Updater_Nam != 'S_S_U' THEN
The same goes for INSERTING. Is:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' OR :New.Row_Updater_Nam !='S_S_U' THEN
Should be:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' AND :New.Row_Updater_Nam !='S_S_U' THEN
I'm assumint that ROW_UPDATER_NAM column is not nullable. Otherwise you may have issues with those conditions, when it is NULL.
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
You would have to give us something more, to be able to test the problem. E.g. the full data structures (CREATE TABLE) of both tables (abc.E_A_R_L_Tandabc.aud) and a sampleinsert/updatestatements onabc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).
– Hilarion
Nov 22 '18 at 6:21
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53383552%2fhow-to-get-the-trigger-the-way-logic-wants%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
It seems like a simple mistake in the logical operators used. You have
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' OR :Old.Row_Updater_Nam != 'S_S_U' THEN
While you should be using AND instead of OR:
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' AND :Old.Row_Updater_Nam != 'S_S_U' THEN
The same goes for INSERTING. Is:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' OR :New.Row_Updater_Nam !='S_S_U' THEN
Should be:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' AND :New.Row_Updater_Nam !='S_S_U' THEN
I'm assumint that ROW_UPDATER_NAM column is not nullable. Otherwise you may have issues with those conditions, when it is NULL.
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
You would have to give us something more, to be able to test the problem. E.g. the full data structures (CREATE TABLE) of both tables (abc.E_A_R_L_Tandabc.aud) and a sampleinsert/updatestatements onabc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).
– Hilarion
Nov 22 '18 at 6:21
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
|
show 1 more comment
1
It seems like a simple mistake in the logical operators used. You have
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' OR :Old.Row_Updater_Nam != 'S_S_U' THEN
While you should be using AND instead of OR:
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' AND :Old.Row_Updater_Nam != 'S_S_U' THEN
The same goes for INSERTING. Is:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' OR :New.Row_Updater_Nam !='S_S_U' THEN
Should be:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' AND :New.Row_Updater_Nam !='S_S_U' THEN
I'm assumint that ROW_UPDATER_NAM column is not nullable. Otherwise you may have issues with those conditions, when it is NULL.
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
You would have to give us something more, to be able to test the problem. E.g. the full data structures (CREATE TABLE) of both tables (abc.E_A_R_L_Tandabc.aud) and a sampleinsert/updatestatements onabc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).
– Hilarion
Nov 22 '18 at 6:21
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
|
show 1 more comment
1
1
1
It seems like a simple mistake in the logical operators used. You have
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' OR :Old.Row_Updater_Nam != 'S_S_U' THEN
While you should be using AND instead of OR:
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' AND :Old.Row_Updater_Nam != 'S_S_U' THEN
The same goes for INSERTING. Is:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' OR :New.Row_Updater_Nam !='S_S_U' THEN
Should be:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' AND :New.Row_Updater_Nam !='S_S_U' THEN
I'm assumint that ROW_UPDATER_NAM column is not nullable. Otherwise you may have issues with those conditions, when it is NULL.
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
It seems like a simple mistake in the logical operators used. You have
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' OR :Old.Row_Updater_Nam != 'S_S_U' THEN
While you should be using AND instead of OR:
IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' AND :Old.Row_Updater_Nam != 'S_S_U' THEN
The same goes for INSERTING. Is:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' OR :New.Row_Updater_Nam !='S_S_U' THEN
Should be:
ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' AND :New.Row_Updater_Nam !='S_S_U' THEN
I'm assumint that ROW_UPDATER_NAM column is not nullable. Otherwise you may have issues with those conditions, when it is NULL.
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
answered Nov 19 '18 at 23:18
HilarionHilarion
596216
596216
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
You would have to give us something more, to be able to test the problem. E.g. the full data structures (CREATE TABLE) of both tables (abc.E_A_R_L_Tandabc.aud) and a sampleinsert/updatestatements onabc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).
– Hilarion
Nov 22 '18 at 6:21
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
|
show 1 more comment
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
You would have to give us something more, to be able to test the problem. E.g. the full data structures (CREATE TABLE) of both tables (abc.E_A_R_L_Tandabc.aud) and a sampleinsert/updatestatements onabc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).
– Hilarion
Nov 22 '18 at 6:21
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
I tried using AND instead of OR, In that scenario, it is not inserting at all no matter what the value is for row_updater_nam column.
– user162139
Nov 20 '18 at 16:57
You would have to give us something more, to be able to test the problem. E.g. the full data structures (
CREATE TABLE) of both tables (abc.E_A_R_L_T and abc.aud) and a sample insert/update statements on abc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).– Hilarion
Nov 22 '18 at 6:21
You would have to give us something more, to be able to test the problem. E.g. the full data structures (
CREATE TABLE) of both tables (abc.E_A_R_L_T and abc.aud) and a sample insert/update statements on abc.E_A_R_L_T. Maybe even your sequence (MDM_SEQ_NBR) definition (esp. that it is not qualified with schema, while all other objects are).– Hilarion
Nov 22 '18 at 6:21
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.audit ( MDM_IUD_SEQ_NBR NUMBER(20) NOT NULL, ENTITY_ID VARCHAR2(20 BYTE) NOT NULL, ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL, DATA_CHANGE_CD VARCHAR2(5 BYTE) NOT NULL, TABLE_NAME VARCHAR2(100 BYTE) NOT NULL, PRIMARY_KEY_COL VARCHAR2(50 BYTE), SECONDARY_KEY_COL VARCHAR2(50 BYTE), PRIMARY_KEY_VAL VARCHAR2(50 BYTE), ROW_PROCESS_CD VARCHAR2(1 BYTE) NOT NULL, ERROR_MSG VARCHAR2(256 BYTE), ROW_INSERT_DT TIMESTAMP(6) NOT NULL, ROW_UPDATE_DT TIMESTAMP(2), ROW_UPDATER_NAM VARCHAR2(50 BYTE) );
– user162139
Nov 23 '18 at 23:02
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
CREATE TABLE abc.E,A,R.L.T ( 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) ;
– user162139
Nov 23 '18 at 23:03
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
SELECT * FROM abc.E.A.R.L.T where addr_seq_nbr = '001'; UPDATE abc.E.A.R.L.T SET ROW_UPDATER_NAM = 'M_S_U' WHERE ADDR_SEQ_NBR = '001' AND ADDR_TYPE_CD = 'POST' and addr_role_cd = 'PHYSICAL'; SELECT * FROM abc.audit where entity_id = '994011355416' ; When I am updating ROW_UPDATER_NAM column as mentioned above, It is firing trigger but it should not because I have used row_updater_nam != 'M_S_U' or 'S_S_U'. Other than msu and ssu, for whatever value, it should update/insert new record in audit table by firing trigger. Hope this will help. Thank You
– user162139
Nov 23 '18 at 23:10
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53383552%2fhow-to-get-the-trigger-the-way-logic-wants%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Use an AFTER trigger, not a BEFORE.
– Bob Jarvis
Nov 20 '18 at 3:06