DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: triggers for update of a clob?

  1. #1
    Join Date
    Jun 2003
    Posts
    47

    Question triggers for update of a clob?

    hi everybody,

    Can anyone tell me how can I make a trigger which triggers when a clob is updated???

    Thanks
    edli

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I don't think it should be any different from any other column with any other datatype.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Documentation says ...
    You can reference object columns or their attributes, or varray, nested table, or LOB columns.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2003
    Posts
    47
    I have this trigger:

    CREATE OR REPLACE TRIGGER TBLWCONT_AI_ROW AFTER
    INSERT OR
    UPDATE OF CONTENT, IDDOCUMENT,IDUSERWHOCREATE,
    IDWORKINGCONTENT
    ON TBLWORKINGCONTENT
    FOR EACH ROW begin
    if inserting then
    INSERT INTO tblworkingcontentversion (idworkingcontentversion,content,IdWorkingContent,IdUser,MinorVersion, idDocument) values
    (sq_tblwcontvers.nextval,:new.content,:new.idworkingcontent,:new.iduserwhocreate,1,:new.iddocument);
    end if;
    if UPDATING then
    INSERT INTO tblworkingcontentversion (idworkingcontentversion,Content,IdWorkingContent,IdUser, idDocument) values
    (sq_tblwcontvers.nextval,:new.content,:new.idworkingcontent,:new.iduserwhocreate,:new.iddocument);
    end if;
    end;



    content is a clob type.

    And I had the following error:

    ORA-25006: cannot specify this column in UPDATE OF clause
    Cause: Attempt to create a trigger on update of a column whose datatype is disallowed in the clause, such as LOB and nested table.

    I don't know how to do this because that's just what I want to do.
    A trigger that trggers on the update of the clob datattype.

    Please help

    Edli

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Get rid of 'OF ' part. Trigger will fire on all updates on table and you will have to perform some manual comparison in trigger code to find out which columns changed.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #6
    Join Date
    Jun 2003
    Posts
    47
    like this for example:

    CREATE OR REPLACE TRIGGER TBLWCONT_AI_ROW AFTER
    INSERT OR
    UPDATE
    ON TBLWORKINGCONTENT
    FOR EACH ROW begin
    if inserting then
    INSERT INTO tblworkingcontentversion (idworkingcontentversion,content,IdWorkingContent,IdUser,MinorVersion, idDocument) values
    (sq_tblwcontvers.nextval,:new.content,:new.idworkingcontent,:new.iduserwhocreate,1,:new.iddocument);
    end if;
    if UPDATING then
    if :new.content<>old.content then
    INSERT INTO tblworkingcontentversion (idworkingcontentversion,Content,IdWorkingContent,IdUser, idDocument) values
    (sq_tblwcontvers.nextval,:new.content,:new.idworkingcontent,:new.iduserwhocreate,:new.iddocument);
    end if;
    end if;
    end;


    it gives me the same error as before can not specify this column in
    update clause.

    What else can I do?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    what version are you on?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2003
    Posts
    47
    Version 8.1.7

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Last edited by TomazZ; 07-02-2003 at 11:28 AM.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  10. #10
    Join Date
    Jun 2003
    Posts
    47
    I haven't been able to resolve this thing yet.
    If anyone has ever done this before please tell me how can I come around it.

    Thank you.

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