DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Startup Trigger

  1. #1
    Join Date
    Dec 2007
    Posts
    9

    Startup Trigger

    I am trying to truncate a sys-owned custom table called 'IS_BACKUP_RUNNING'.

    I have used a sys startup trigger but it doesn't seem to be working.

    ex:
    CREATE OR REPLACE TRIGGER CLEAR_BACKUP_FLAG
    AFTER STARTUP on database
    begin
    execute immediate 'TRUNCATE TABLE IS_BACKUP_RUNNING';
    exception when others then null;
    end;
    /
    SHOW ERRORS;

    I bounced the instance, but the table isn't truncated. Is there a restriction on DDL for these types of triggers?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Why don't you move the table to a non Oracle schema? I'm not sure about the trigger restriction, but it seems like you want to separate out your stuff from that of Oracle.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    1- I would write a proper EXCEPTION section, be sure to show the Oracle account executing the trigger alongside with the actual error.

    2- I would fully qualify the target table.

    3- I would be sure ORACLE account has the right privs on target table.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Dec 2007
    Posts
    9
    I am having trouble capturing the EXCEPTION b/c it fires on startup, co I can't use DBMS_OUTPUT... any ideas on being able to insert SQLERRM into a log table?

    I have heard that DDL is not allowed on startup triggers. Can anybody confirm?

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    just use an insert statement, thats dml not ddl

  6. #6
    Join Date
    Dec 2007
    Posts
    9
    DML is definitely out in STARTUP TRIGGERS, but thanks.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by sqlsurf
    DML is definitely out in STARTUP TRIGGERS, but thanks.
    You can send messages to the alert log.

    http://www.oracle.com/technology/ora...03/011203.html

Posting Permissions

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


Click Here to Expand Forum to Full Width