rem mvtrunc_lock: DDL Trigger to prevent truncation of a table with the materialized view log
rem (c) Go-Faster Consultancy Ltd.
rem 1.11.2006 initial version

set echo on feedback on verify on lines 100 timi on pause off
spool mvtrunc_lock

CREATE OR REPLACE TRIGGER mvtrunc_lock 
BEFORE TRUNCATE
ON SYSADM.SCHEMA
DECLARE
 e_generate_message EXCEPTION;
 l_recname  VARCHAR2(15 CHAR);
 l_msg      VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2     VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent||' '||lower(ora_dict_obj_type)
			||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||'.  ';

 sql_text ora_name_list_t;
 l_sql_stmt VARCHAR2(1000 CHAR) := ''; 
 n          INTEGER;
 i          INTEGER;

BEGIN
 /*extract the originating SQL statement into a string variable*/
 n := ora_sql_txt(sql_text);
 FOR i IN 1..n LOOP
  l_sql_stmt := SUBSTR(l_sql_stmt || sql_text(i),1,1000);
 END LOOP;

 IF ora_dict_obj_type = 'TABLE' AND ora_sysevent = 'TRUNCATE' THEN 

  BEGIN /*if a materialized view log exists*/
   SELECT 'There is a materialized view log.'
   INTO   l_msg
   FROM   all_mview_logs l
   WHERE  ROWNUM = 1
   AND    l.master = ora_dict_obj_name
   AND    l.log_owner = ora_dict_obj_owner
   ;
   RAISE e_generate_message;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;
 END IF;

EXCEPTION
 WHEN NO_DATA_FOUND THEN 
  NULL;
 WHEN e_generate_message THEN
  RAISE_APPLICATION_ERROR(-20042,'MVTRUNC_LOCK: '||l_msg2||l_msg||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors

ALTER TRIGGER mvtrunc_lock DISABLE;
DROP TABLE t_pk;
DROP MATERIALIZED VIEW t_pk_mv;
DROP TABLE t_r;
DROP MATERIALIZED VIEW t_r_mv;

CREATE TABLE t_pk
(a NUMBER
,b VARCHAR2(20)
,CONSTRAINT t PRIMARY KEY(a));

CREATE TABLE t_r
(a NUMBER
,b VARCHAR2(20));

CREATE MATERIALIZED VIEW LOG ON t_pk WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW t_pk_mv REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM t_pk ;

CREATE MATERIALIZED VIEW LOG ON t_r WITH ROWID;
CREATE MATERIALIZED VIEW t_r_mv REFRESH FAST WITH ROWID AS SELECT * FROM t_r ;

INSERT INTO t_pk VALUES(1,'Old');
INSERT INTO t_pk VALUES(2,'Old');
INSERT INTO t_r VALUES(1,'Old');
INSERT INTO t_r VALUES(2,'Old');

SELECT * FROM t_pk;
SELECT * FROM mlog$_t_pk;
SELECT * FROM t_r;
SELECT * FROM mlog$_t_r;

exec dbms_mview.refresh(list => 'T_PK_MV', method => 'f');
exec dbms_mview.refresh(list => 'T_R_MV', method => 'f');
SELECT * FROM t_pk_mv;
SELECT * FROM t_r_mv;

TRUNCATE TABLE t_pk;
TRUNCATE TABLE t_r;

INSERT INTO t_pk VALUES(2,'New');
INSERT INTO t_pk VALUES(3,'New');
INSERT INTO t_r VALUES(2,'New');
INSERT INTO t_r VALUES(3,'New');

exec dbms_mview.refresh(list => 'T_PK_MV', method => 'f');
exec dbms_mview.refresh(list => 'T_R_MV', method => 'f');

REM but row 1 is in MV but not source table
SELECT * FROM t_pk;
SELECT * FROM t_pk_mv;
SELECT * FROM t_r;
SELECT * FROM t_r_mv;

exec dbms_mview.refresh(list => 'T_PK_MV', method => 'c');
exec dbms_mview.refresh(list => 'T_R_MV', method => 'c');
REM a full refresh corrects this

SELECT * FROM t_pk_mv;
SELECT * FROM t_r_mv;

ALTER TRIGGER mvtrunc_lock ENABLE;
TRUNCATE TABLE t_pk /*this will error*/;
DROP MATERIALIZED VIEW LOG on t;
TRUNCATE TABLE t_pk /*this will succeed*/;
DROP TABLE t_pk;
DROP MATERIALIZED VIEW t_pk_mv;
DROP TABLE t_r;
DROP MATERIALIZED VIEW t_r_mv;

spool off