Forum Stats

  • 3,839,387 Users
  • 2,262,486 Discussions
  • 7,900,956 Comments

Discussions

Two triggers on a table now processes won't work

Idkfa2
Idkfa2 Member Posts: 137 Red Ribbon
edited Dec 27, 2017 12:21PM in APEX Discussions

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
/
 
Tagged:

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,101 Red Diamond
    edited Dec 26, 2017 4:02PM
    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.

  • Idkfa2
    Idkfa2 Member Posts: 137 Red Ribbon
    edited Dec 27, 2017 9:44AM

    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.

  • Idkfa2
    Idkfa2 Member Posts: 137 Red Ribbon
    edited Dec 27, 2017 12:21PM

    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?

This discussion has been closed.