Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: Trigger on drop table

    Hi,
    I want to create a trigger on dropping a table.
    I tried creating a simple trigger to display a line of text on dropping a table, but i get the error "that system triggers cannot be based on tables or views".

    I created a table "table1" and insert few records.
    Then I tried creating a trigger drop_table1


    SQL> create or replace trigger drop_table1
    before drop on table1
    begin
    dbms_output.put_line('table1 about to be dropped');
    end;
    /

    This didnt work at all.

    Then i tried creating another trigger like

    create or replace trigger drop_table1
    before drop
    begin
    dbms_output.put_line('table1 about to be dropped');
    end;
    /

    it said missing ON keyword.

    I would really appreciate help on this.
    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger on drop table

    Originally posted by aruneeshsalhotr
    Hi,
    I want to create a trigger on dropping a table.
    I tried creating a simple trigger to display a line of text on dropping a table, but i get the error "that system triggers cannot be based on tables or views".

    I created a table "table1" and insert few records.
    Then I tried creating a trigger drop_table1


    SQL> create or replace trigger drop_table1
    before drop on table1
    begin
    dbms_output.put_line('table1 about to be dropped');
    end;
    /

    This didnt work at all.

    Then i tried creating another trigger like

    create or replace trigger drop_table1
    before drop
    begin
    dbms_output.put_line('table1 about to be dropped');
    end;
    /

    it said missing ON keyword.

    I would really appreciate help on this.
    Thanx and Regards
    Aruneesh
    Have you read the CREATE TRIGGER section of the SQL Reference Manual? It is here:

    http://technet.oracle.com/docs/produ...6a.htm#2111136

    You can create a trigger that fires after ANY drop statement, but not for a particular table only. To find out which table was dropped, you would look at the event attributes, which are described here:

    http://technet.oracle.com/doc/oracle...vt.htm#1000872

    For example, ora_dict_obj_name has the table name.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Try modifying this procedure , just replace X with your schema name.

    Alan

    CREATE OR REPLACE TRIGGER SYSTEM.DROPTABLE_X
    BEFORE DROP
    ON X.SCHEMA
    DECLARE
    /************************************************** ****************************
    NAME:
    PURPOSE:
    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 11/07/2003 AlanP 1. Created this trigger.
    NOTES: Replace X with your schema
    ************************************************** ****************************/
    v_msg VARCHAR2(1000) :=
    'Contact DBA, No drop allowed on ' ||
    ora_dict_obj_owner || '.' ||
    ora_dict_obj_name || ' from ' ||
    ORA_LOGIN_USER;
    v_numrows number;
    BEGIN
    IF upper(ora_dict_obj_name) NOT LIKE 'STG$'
    AND upper(ora_dict_obj_name) NOT LIKE 'BKP%'
    AND upper(ora_dict_obj_name) NOT LIKE 'BACKUP%'
    AND upper(ora_dict_obj_name) NOT LIKE 'TEMP%'
    THEN
    SELECT num_rows into v_numrows from sys.dba_tables where owner=ora_dict_obj_owner and table_name=ora_dict_obj_name;
    sys.dbms_system.ksdwrt(2,v_msg);
    IF v_numrows<100000 or v_numrows is null THEN
    EXECUTE IMMEDIATE 'create table drop_'||ora_dict_obj_name||' as select * from '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
    ELSE
    RAISE_APPLICATION_ERROR (-20905, v_msg);
    END IF;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END ;
    Last edited by AlanP; 07-15-03 at 13:04.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •