Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Two triggers on a table now processes won't work

Hello all,
I've added a trigger to a table that already had a trigger. The trigger I added is to insert new values into a table I created. After placing the trigger I created, now the page won't process. If I remove the trigger I created, the page will process.
Is there a reason why there cannot be two triggers on this table?
Here is what the SQL for that table looks like including the two triggers:
CREATE TABLE "CR_MDT_VERIFY" ( "CR_MDT_VERIFY_ID" NUMBER(30,0) NOT NULL ENABLE, "FK_CR_MDT_ID" NUMBER(30,0) NOT NULL ENABLE, "VERIFICATION_TYPE" VARCHAR2(255), "VERIFIED_BY" VARCHAR2(255), "CREATED" TIMESTAMP (6) WITH LOCAL TIME ZONE, "CREATED_BY" VARCHAR2(255), "UPDATED" TIMESTAMP (6) WITH LOCAL TIME ZONE, "UPDATED_BY" VARCHAR2(255), "DATA_COLLECTOR" VARCHAR2(30), "DATE_VERIFIED" DATE, CONSTRAINT "CR_MDT_VERIFY_PK" PRIMARY KEY ("CR_MDT_VERIFY_ID") USING INDEX ENABLE )/ALTER TABLE "CR_MDT_VERIFY" ADD CONSTRAINT "CR_MDT_VERIFY_FK" FOREIGN KEY ("FK_CR_MDT_ID")REFERENCES "CR_MDT" ("CR_MDT_ID") ON DELETE CASCADE ENABLE/
CREATE OR REPLACE TRIGGER "BI_CR_MDT_VERIFY"
before insert or update on CR_MDT_VERIFY
for each row
begin
if :NEW.CR_MDT_VERIFY_ID is null then
select CR_MDT_VERIFY_SEQ.nextval into :NEW.CR_MDT_VERIFY_ID from sys.dual;
end if;
if inserting then
:new.created := localtimestamp;
:new.created_by := nvl(wwv_flow.g_user,user);
:new.updated := localtimestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
elsif inserting or updating then
:new.updated := localtimestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
end;
/
ALTER TRIGGER "BI_CR_MDT_VERIFY" ENABLE
/
CREATE OR REPLACE TRIGGER "CR_MDT_VERIFY_AUDIT"
before insert or delete or update on CR_MDT_VERIFY
for each row
declare
V_user varchar2 (30);
V_date constant cr_mdt_VERIFY_AUDIT.entry_date%type := sysdate;
begin
IF inserting THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (:NEW.created, NULL, v_user, v_date, 'DE1 Completed', :OLD.FK_CR_MDT_ID);
ELSIF INSERTING THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (null, :NEW.UPDATED, v_user, v_date, 'DE2 Completed',:OLD.FK_CR_MDT_ID);
ELSIF UPDATING THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (:NEW.created, :OLD.created, v_user, v_date, 'DE1 Completed', :OLD.FK_CR_MDT_ID);
ELSIF UPDATING THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (:NEW.updated, :OLD.updated, v_user, v_date, 'DE2 Completed', :OLD.FK_CR_MDT_ID);
END IF;
END;
/
ALTER TRIGGER "CR_MDT_VERIFY_AUDIT" ENABLE
/
Answers
-
Idkfa2 wrote:Hello all,I've added a trigger to a table that already had a trigger. The trigger I added is to insert new values into a table I created. After placing the trigger I created, now the page won't process. If I remove the trigger I created, the page will process.Is there a reason why there cannot be two triggers on this table? Here is what the SQL for that table looks like including the two triggers:CREATE TABLE "CR_MDT_VERIFY" ( "CR_MDT_VERIFY_ID" NUMBER(30,0) NOT NULL ENABLE, "FK_CR_MDT_ID" NUMBER(30,0) NOT NULL ENABLE, "VERIFICATION_TYPE" VARCHAR2(255), "VERIFIED_BY" VARCHAR2(255), "CREATED" TIMESTAMP (6) WITH LOCAL TIME ZONE, "CREATED_BY" VARCHAR2(255), "UPDATED" TIMESTAMP (6) WITH LOCAL TIME ZONE, "UPDATED_BY" VARCHAR2(255), "DATA_COLLECTOR" VARCHAR2(30), "DATE_VERIFIED" DATE, CONSTRAINT "CR_MDT_VERIFY_PK" PRIMARY KEY ("CR_MDT_VERIFY_ID") USING INDEX ENABLE )/ALTER TABLE "CR_MDT_VERIFY" ADD CONSTRAINT "CR_MDT_VERIFY_FK" FOREIGN KEY ("FK_CR_MDT_ID")REFERENCES "CR_MDT" ("CR_MDT_ID") ON DELETE CASCADE ENABLE/
CREATE OR REPLACE TRIGGER "BI_CR_MDT_VERIFY"
before insert or update on CR_MDT_VERIFY
for each row
begin
if :NEW.CR_MDT_VERIFY_ID is null then
select CR_MDT_VERIFY_SEQ.nextval into :NEW.CR_MDT_VERIFY_ID from sys.dual;
end if;
if inserting then
:new.created := localtimestamp;
:new.created_by := nvl(wwv_flow.g_user,user);
:new.updated := localtimestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
elsif inserting or updating then
:new.updated := localtimestamp;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
end;
/
ALTER TRIGGER "BI_CR_MDT_VERIFY" ENABLE
/CREATE OR REPLACE TRIGGER "CR_MDT_VERIFY_AUDIT"
before insert or delete or update on CR_MDT_VERIFY
for each row
declare
V_user varchar2 (30);
V_date constant cr_mdt_VERIFY_AUDIT.entry_date%type := sysdate;
begin
IF inserting THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (:NEW.created, NULL, v_user, v_date, 'DE1 Completed', :OLD.FK_CR_MDT_ID);
ELSIF INSERTING THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (null, :NEW.UPDATED, v_user, v_date, 'DE2 Completed',:OLD.FK_CR_MDT_ID);
ELSIF UPDATING THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (:NEW.created, :OLD.created, v_user, v_date, 'DE1 Completed', :OLD.FK_CR_MDT_ID);
ELSIF UPDATING THEN
insert into CR_MDT_VERIFY_AUDIT (new_value, old_value, user_name, entry_date, operation, FK_CR_MDT_ID)
VALUES (:NEW.updated, :OLD.updated, v_user, v_date, 'DE2 Completed', :OLD.FK_CR_MDT_ID);
END IF;
END;
/
ALTER TRIGGER "CR_MDT_VERIFY_AUDIT" ENABLE
/Does the second trigger actually compile? Having a table and trigger with the same name doesn’t seem like a good idea...
Why the multiple INSERTING/UPDATING predicates? That logic is incorrect as only a single IF...ELSIF... branch will be executed.
Do the triggers executed successfully when DML statements are executed from the SQL*Plus, SQL Developer, or SQL Workshop command line?
Why doesn’t the page process successfully? Use debug mode to trace execution on page submission.
-
Happy New Years to you fac586.
Okay I see it using the debug mode.
- ora_sqlerrm: ORA-01400: cannot insert NULL into ("SOCRD_POC2"."CR_MDT_VERIFY_AUDIT"."FK_CR_MDT_ID")
ORA-06512: at "SOCRD_POC2.CR_MDT_VERIFY_AUDIT", line 6
ORA-04088: error during execution of trigger 'SOCRD_POC2.CR_MDT_VERIFY_AUDIT'
I should fix the INSERTING/UPDATING to shorten it. You're right. I followed a Youtube tutorial and didn't want to mess with it since it was already working, but I think I can see where to take out the necessary ones.
-
fac586,
Can you help me with the syntax above please? My column for the second trigger UPDATED is not putting a value in the table I created. I am assuming it is because the value is in LOCALTIMESTAMP format and my column format for :NEW.UPDATED and :OLD.UPDATED is DATE. How can I convert the original value from LOCALTIMESTAMP to DATE so that my table populates?