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
/