Working with Oracle Database 12.1.0.2.0
I have created a table and a trigger. The trigger is predicated on an UPDATE to the table. The trigger should run a custom procedure. It does not do so. If I omit the PRAGMA AUTONOMOUS_TRANSACTION command, I get an error telling me that I cannot commit inside of a trigger. If I include that command, nothing happens. Perhaps someone can give me a clue. Here is all of the code:
CREATE TABLE MT_TRIGGER_TABLE
( "LOAD_DATE" DATE,
"AXIS" VARCHAR2(20 BYTE),
"CLEARVIEW" VARCHAR2(20 BYTE),
"WAREHOUSE" VARCHAR2(20 BYTE)
) ;
CREATE UNIQUE INDEX MT_TRIGGER_PK ON MT_TRIGGER_TABLE (LOAD_DATE) ;
CREATE OR REPLACE TRIGGER TG_UPDATE_DAILY_MT
BEFORE UPDATE ON MT_TRIGGER_TABLE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
HV_UPDATE_DAILY_MT;
END TG_UPDATE_DAILY_MT;
/
ALTER TRIGGER TG_UPDATE_DAILY_MT ENABLE;
create or replace procedure HV_UPDATE_DAILY_MT as
v_ok_to_load number;
BEGIN
select count(*)
into v_ok_to_load from MT_TRIGGER_TABLE
where LOAD_DATE = TRUNC(SYSDATE)
and AXIS is not null
and CLEARVIEW is not null
and WAREHOUSE is null;
IF v_ok_to_load > 0
THEN
update MT_TRIGGER_TABLE
set WAREHOUSE = TO_CHAR(CURRENT_TIMESTAMP,'MM/DD/YYYY HH:MI:ss')
where LOAD_DATE = TRUNC(SYSDATE);
dbms_mview.refresh('MARK_TEST',atomic_refresh=>TRUE);
END IF;
END HV_UPDATE_DAILY_MT;
DELETE FROM MT_TRIGGER_TABLE;
INSERT INTO MT_TRIGGER_TABLE (LOAD_DATE) VALUES(TRUNC(SYSDATE));
UPDATE MT_TRIGGER_TABLE SET AXIS = TO_CHAR(CURRENT_TIMESTAMP,'MM/DD/YYYY HH:MI:ss') WHERE LOAD_DATE = TRUNC(SYSDATE);
UPDATE MT_TRIGGER_TABLE SET CLEARVIEW = TO_CHAR(CURRENT_TIMESTAMP,'MM/DD/YYYY HH:MI:ss') WHERE LOAD_DATE = TRUNC(SYSDATE);
When that second UPDATE occurs, the conditions are met for the trigger to happen. I should see the value of WAREHOUSE populated, and the materialized view should be refreshed. Neither one happens.
I can run the procedure HV_UPDATE_DAILY_MT manually with no problem. If either the AXIS or CLEARVIEW columns are null, the procedure does not refresh the materialized view, which is correct. If both AXIS and CLEARVIEW are populated and the WAREHOUSE column is null, the materialized view gets refreshed, which is correct. So I know the procedure does what I want it to do. I am simply unable to make it run as a result of the trigger firing.
HELP!!