Two triggers on a table now processes won't work

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.