Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Create Or Replace Trigger

    Was one of the intentions of the REPLACE option in CREATE OR REPLACE TRIGGER intended to allow for updates to existing triggers while users are still in the system?

    Just wondering if emergency updates to a trigger during normal business hours would corrupt anyone's session if they were currently running DML involving the trigger while you're trying to REPLACE it.

    Thanks,
    Chuck

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If they were currently using the trigger, they would use the old version, the next time they hit the trigger, they would use the new version.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    I like the thought, but I don't know if Oracle really had that in mind at the time they added the OR REPLACE clause to the CREATE TRIGGER command syntax. I believe they just wanted to implement an "update" capability. So, instead of creating a whole new set of commands like UPDATE TRIGGER they added the OR REPLACE clause to the existing CREATE TRIGGER command.

    An UPDATE TRIGGER command would have been interesting ... imagine saying
    UPDATE TRIGGER x LINE 47
    REPLACE 'sysdate'
    WITH 'systimestamp';

    Cheers,

    -Mark

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Perhaps 'intention' was the wrong word. I was just wondering if the REPLACE option would serve the purpose of updating a trigger currently in use by our user community.

  5. #5
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    OK, then the answer is "no." If you are modifying an ON UPDATE trigger while an update transaction is in progress, your trigger command will be queued and not implemented until after the update statement completes.

    If, on the other hand, your trigger is a simple ON INSERT trigger, then while the user is doing an update the trigger can be created or replaced at any time without waiting.

    p.s., I downloaded another DBForum user's SMBIN table example with all 400,801 rows to test you question. My computer was updating all rows faster than I could copy/paste trigger code into a SQL*Plus window. I therefore had to do "INSERT INTO SMBIN SELECT * FROM SMBIN" a few times to double and then redouble the number of rows. Finally, when I had 10 million rows the updates were taking long enough for me to have time to copy/paste new trigger code into the SQL*Plus for Windows. I did not have time to query the DBA_WAITERS and DBA_BLOCKERS views, which I would normally do.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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