I create a table in my database. Now i need to block the drop operation for that table.. pls give some tips.

Hi

Please try this ....


CREATE TABLE security AS SELECT object_name, owner FROM dba_objects WHERE object_type='TABLE' ;

CREATE OR REPLACE TRIGGER trg_security BEFORE DROP ON DATABASE
DECLARE
v_object_name char(128);
v_owner varchar2(128);
BEGIN
SELECT object_name INTO v_object_name FROM security WHERE UPPER(object_name) = ora_dict_obj_name and owner = login_user;
IF SQL%FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'Cannot drop protected table');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- Allows to drop that table
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20002, 'Remove Duplicate entry from security table');
END;