Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How can I insert into a table after an insert event?

Hello,
This is the trigger I am using that listens for an INSERT or UPDATE event from table CRPDTA.F03012
where it should insert the data in table CRPDTA.F_ASR_USERS
the fields F_ASR_USERS.USER_ID(F03012.AIAN8)
, F_ASR_USERS.USER_IDENTITY(F0101.ABTAX)
, F_ASR_USERS.USER_EMAIL(F01151.EAEMAL)
and F_ASR_USERS.USER_PASSWORD(F0101.ABTAX)
, this is my trigger:
create or replace TRIGGER CRPDTA.INSERTUSER AFTER insert or update ON CRPDTA.F03012 FOR EACH ROW DECLARE USER_ID NUMBER := :NEW.AIAN8; USER_IDENTITY VARCHAR2(100); USER_EMAIL VARCHAR2(100); BEGIN SELECT ABTAX INTO USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :USER_ID; SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE ABAN8 = :USER_ID; IF USER_IDENTITY IS NULL IF USER_EMAIL IS NULL UPDATE CRPDTA.F_ASR_USERS F_ASR_USERS SET F_ASR_USERS.USER_EMAIL = :USER_EMAIL WHERE F_ASR_USERS.USER_IDENTITY = :USER_IDENTITY ELSE INSERT INTO CRPDTA.F_ASR_USERS (USER_ID, USER_IDENTITY,USER_EMAIL, USER_PASSWORD) VALUES (:USER_ID, :USER_IDENTITY,:USER_EMAIL, CUSTOM_HASH(:USER_IDENTITY,256)) END IF; COMMIT; ELSE RAISE_APPLICATION_ERROR(-20000,'AN8 NOT EXIST ADDRESS BOOK MASTER') END IF; END;
The problem is that I have a bug, and I'm not sure if it works the way I put it, and I can't find in forums more or less the case I have.
And this is the error I have:
Oracle version: 19.0.0.0.0 - Production
I hope you can help me, thank you.
Best Answer
-
Whenever you have a question, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include CREATE TABLE statements for all the tables needed.
Remember to put a THEN after the second IF as well:
create or replace TRIGGER CRPDTA.INSERTUSER AFTER insert or update ON CRPDTA.F03012 FOR EACH ROW DECLARE USER_ID NUMBER := :NEW.AIAN8; USER_IDENTITY VARCHAR2 (100); USER_EMAIL VARCHAR2( 100); BEGIN SELECT ABTAX INTO USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :USER_ID; SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE ABAN8 = :USER_ID; IF USER_IDENTITY IS NULL THEN IF USER_EMAIL IS NULL THEN UPDATE CRPDTA.F_ASR_USERS -- no need for alias SET F_ASR_USERS.USER_EMAIL = :USER_EMAIL WHERE F_ASR_USERS.USER_IDENTITY = :USER_IDENTITY; ELSE INSERT INTO CRPDTA.F_ASR_USERS (USER_ID, USER_IDENTITY, USER_EMAIL, USER_PASSWORD) VALUES (:USER_ID, :USER_IDENTITY,:USER_EMAIL, CUSTOM_HASH (:USER_IDENTITY, 256)); END IF; -- Don't COMMIT in triggers ELSE RAISE_APPLICATION_ERROR (-20000, 'AN8 NOT EXIST ADDRESS BOOK MASTER'); END IF; END;
Every IF needs a THEN.
It looks like you're missing some semicolons, too. There could be other errors, also, but I can't test it without the tables.
Answers
-
correct syntax is:
if IF USER_IDENTITY IS NULL then ... else ... end if;
And don't use commit inside a trigger.
-
Hi @ascheffer ,
Thanks, This is how the syntax was left, and it does not mark me an error:
create or replace TRIGGER CRPDTA.INSERTUSER AFTER insert or update ON CRPDTA.F03012 FOR EACH ROW DECLARE USER_ID VARCHAR2(100); USER_IDENTITY VARCHAR2(100); USER_EMAIL VARCHAR2(100); BEGIN SELECT ABAN8,ABTAX INTO USER_ID,USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :new.AIAN8; SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE EAAN8 = USER_ID; IF USER_IDENTITY IS NULL THEN IF USER_EMAIL IS NULL THEN UPDATE CRPDTA.F_ASR_USERS F_ASR_USERS SET F_ASR_USERS.USER_EMAIL = USER_EMAIL WHERE F_ASR_USERS.USER_IDENTITY = :new.AIAN8; END IF; ELSE INSERT INTO CRPDTA.F_ASR_USERS (USER_ID, USER_IDENTITY,USER_EMAIL, USER_PASSWORD) VALUES (USER_ID, USER_IDENTITY,USER_EMAIL, CUSTOM_HASH(USER_IDENTITY,256)); END IF; END;
I have a question here:
SELECT ABAN8,ABTAX INTO USER_ID,USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :new.AIAN8;
It is assumed that
:new.AIAN8
when performing the insert would have this value, correct?Example:
And also to remove the
:
VALUES (USER_ID, USER_IDENTITY,USER_EMAIL, CUSTOM_HASH(USER_IDENTITY,256));
-
Whenever you have a question, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include CREATE TABLE statements for all the tables needed.
Remember to put a THEN after the second IF as well:
create or replace TRIGGER CRPDTA.INSERTUSER AFTER insert or update ON CRPDTA.F03012 FOR EACH ROW DECLARE USER_ID NUMBER := :NEW.AIAN8; USER_IDENTITY VARCHAR2 (100); USER_EMAIL VARCHAR2( 100); BEGIN SELECT ABTAX INTO USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :USER_ID; SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE ABAN8 = :USER_ID; IF USER_IDENTITY IS NULL THEN IF USER_EMAIL IS NULL THEN UPDATE CRPDTA.F_ASR_USERS -- no need for alias SET F_ASR_USERS.USER_EMAIL = :USER_EMAIL WHERE F_ASR_USERS.USER_IDENTITY = :USER_IDENTITY; ELSE INSERT INTO CRPDTA.F_ASR_USERS (USER_ID, USER_IDENTITY, USER_EMAIL, USER_PASSWORD) VALUES (:USER_ID, :USER_IDENTITY,:USER_EMAIL, CUSTOM_HASH (:USER_IDENTITY, 256)); END IF; -- Don't COMMIT in triggers ELSE RAISE_APPLICATION_ERROR (-20000, 'AN8 NOT EXIST ADDRESS BOOK MASTER'); END IF; END;
Every IF needs a THEN.
It looks like you're missing some semicolons, too. There could be other errors, also, but I can't test it without the tables.