Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Question Unanswered: How to RENAME A SEQUENCE IN A TRIGGER?

    Hello,everyone. Thank you in advance.

    My ORACLE problem is described as fellow,

    (1)I have a table MASTERINFO with following fields

    MASTERINFO(SEQID,SEGUENCENAME,SEQDATE)

    And with the following records,

    (111,'CVM_SEQUENCE1',2003-09-09)
    (112,'CVM_SEQUENCE2',2003-09-09)
    (113,'CVM_SEQUENCE3',2003-09-09)

    (2) At the same time, I have a SEQUENCE,which name is changed with value of FIELD SEQUENCENAME in the table MASTERINFO. That is to say, when I update value of FIELD SEQUENCENAME in the table MASTERINFO,the sequence name has to be automatically changed.Fox example, when I change the record with SEQID value 111 with following SQL statement,

    Update MASTERINFO SET SEGUENCENAME='CVM_SEQUENCE1234' WHERE SEQID=111

    I hope the SEQUENCE CVM_SEQUENCE1 with the new name CVM_SEQUENCE1234.

    So I have created a trigger in the table MASTERINFO,

    CREATE OR REPLACE MASTERINFO_TRIGGER
    AFTER UPDATE ON MASTERINFO
    FOR EACH ROW
    BEGIN
    RENAME :OLD.SEGUENCENAME TO :NEW.SEGUENCENAME
    END;

    But I faid. How to create such a trigger to finish the task. Please give me some advice. Thank a lots.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    CREATE OR REPLACE MASTERINFO_TRIGGER
    AFTER UPDATE ON MASTERINFO
    FOR EACH ROW
    BEGIN
    if :OLD.SEGUENCENAME <> :NEW.SEGUENCENAME then
    execute immediate 'rename RENAME '||:OLD.SEGUENCENAME||' TO '||:NEW.SEGUENCENAME ;
    end if;
    END;
    Last edited by beilstwh; 10-26-06 at 14:49.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2006
    Posts
    58
    Thank you, beilstwh.

    However, I failed in Oracle9.2. Many materials show that DDL statements can not be included in Oracle trigger.Right?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I believe it is not about Oracle version (BTW, renaming syntax isn't correct; Bill wasn't renaming a column) and DDLs in triggers. DDL statements perform implicit COMMIT which is not allowed in triggers. "Not" can be turned into "yes" if we use autonomous transaction (which could be appropriate in this case). So, DDL problem can be solved, but the main problem (from my current point of view) is somewhere else: a deadlock.

    This is a trigger:
    Code:
    CREATE OR REPLACE TRIGGER test_grigger
      AFTER UPDATE ON TEST
      FOR EACH ROW
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      IF :OLD.ename <> :NEW.ename
      THEN
        EXECUTE IMMEDIATE 'alter table test rename column ename to ' || :NEW.ename;
      END IF;
    END;
    /
    Here is the UPDATE statement:
    Code:
    SQL> UPDATE TEST SET ename = 'SMITH_20' WHERE ename = 'SMITH';
    UPDATE TEST SET ename = 'SMITH_20' WHERE ename = 'SMITH'
           *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified
    ORA-06512: at "SCOTT.TEST_TRIGGER", line 7
    ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIGGER'
    Now, perhaps there is a way to override this, but I wouldn't know how.

    I tried to
    • include "LOCK TABLE test IN EXCLUSIVE MODE" and give some time for UPDATE to be committed,
    • set autocommit on
    but all my attempts failed.

    Perhaps someone else will know how to help you; I don't.

    However, just being curious: WHY would you want to rename a column through a trigger whenever column value is different than the old one? How do you expect code different than yours (i.e. your currently written code) to work? How would you write a report or a form, for example? Will they be able to dynamically know which column name to use? What about other developers?

    Personally, I don't like the general idea; something stinks here, but I might be wrong. Will know when/if you explain WHY.

  5. #5
    Join Date
    Aug 2006
    Posts
    58
    Thank you,Littlefoot.

    I agree with you.I did not design such a database. However the databse of the project was designed by other, I have to find a way to cope with this problem.Right?

    I am very sorry that I could not describe my problem in details. In my problem, SEQUENCE is a object in Oracle, not a column in table MASTERINFO. There are many objects in Oracle, for exampls, table, view, trigger, stroed procedure....

    Here I want to rename the SEQUENCE in a trigger. How can I do?Please give me more advice. Thank a lot.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A sequence?!? LOL, I thought it was a table column name

    OK, here it is: Let's create testing environment:
    Code:
    CREATE TABLE masterinfo 
    (seqid        NUMBER, 
     sequencename VARCHAR2(20), 
     seqdate      DATE);
    
    INSERT INTO masterinfo VALUES (1, 'SEQ_1', SYSDATE);
    
    CREATE VIEW v_masterinfo AS SELECT * FROM masterinfo;
    
    CREATE SEQUENCE seq_1;
    As you've seen, here's a view created upon our table. Its purpose is to avoid mutating table error (which would appear if we created a trigger on a table). Here is an INSTEAD OF trigger (created on a view):
    Code:
    CREATE OR REPLACE TRIGGER trg_v_seq
      instead OF UPDATE ON v_masterinfo
      FOR EACH ROW
    DECLARE
      l_old_seq MASTERINFO.sequencename%TYPE;
      PRAGMA autonomous_transaction;           -- to allow DDL
    BEGIN
      IF :OLD.sequencename <> :NEW.sequencename
      THEN
         -- find old sequence name
         SELECT sequencename
           INTO l_old_seq
           FROM v_MASTERINFO
           WHERE seqid = :NEW.seqid;
    
         -- update desired record
         UPDATE MASTERINFO SET
           sequencename = :NEW.sequencename
           WHERE seqid = :NEW.seqid;
    
         -- rename sequence to name entered into the 'sequencename'
         EXECUTE IMMEDIATE 'rename ' || l_old_seq || ' to ' || :NEW.sequencename;
    
      END IF;
    END;
    /
    And that's it ... now you only have to perform UPDATE:
    Code:
    UPDATE v_masterinfo SET sequencename = 'SEQ_5' WHERE seqid = 1;

Posting Permissions

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