Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: LOB Trigger Help

    I have a table with a blob and a clob in it. I am trying to write a trigger that allows for only one of them to be filled per row such that if, for example, a row has a blob column value, if a clob column value is inserted into that row, the trigger sets the blob column to null. The following trigger does not compile because apparently you cannot change the value of :new data for lobs.
    CREATE OR REPLACE TRIGGER lobTest_tr
    before insert or update on lobTest
    for each row
    declare
    duplicate_routing EXCEPTION;
    begin
    if(:new.clob_data IS NOT NULL AND dbms_lob.GETLENGTH(:new.clob_data)>0 AND :old.blob_data IS NOT NULL
    AND dbms_lob.GETLENGTH(:old.blob_data)>0)
    THEN
    :new.blob_data := null;
    elsif(:new.blob_data IS NOT NULL AND dbms_lob.GETLENGTH(:new.blob_data)>0 AND :old.clob_data IS NOT NULL
    AND dbms_lob.GETLENGTH(:old.clob_data)>0)
    then
    :new.clob_data := null;
    elsif(:new.blob_data IS NOT NULL AND dbms_lob.GETLENGTH(:new.blob_data)>0 AND :new.clob_data IS NOT NULL
    AND dbms_lob.GETLENGTH(:new.clob_data)>0)
    then raise duplicate_routing;
    END IF;
    EXCEPTION
    when duplicate_routing then
    RAISE_APPLICATION_ERROR(-20999, 'This routing already exists in the table.');
    when OTHERS then
    RAISE_APPLICATION_ERROR(-20999, SQLERRM);
    end lobTest_tr;
    Does anyone know of another way to accomplish this?
    Thanks.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I think it might be easier to have a few different triggers.

    1. instead of update on tablename.blob_column
    2. instead of update on tablename.cloc_column
    3. now worry about what to do if there is an insert
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    the board thinks I didn't reply, strange
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by bernsch
    I have a table with a blob and a clob in it. I am trying to write a trigger that allows for only one of them to be filled per row such that if, for example, a row has a blob column value, if a clob column value is inserted into that row, the trigger sets the blob column to null.
    If you only allow ONE of the BLOB/CLOB columns to have data, why not have only ONE BLOB column? (you can insert Character data in a BLOB and forget the triggers)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2002
    Posts
    5

    Only have one blob?

    Would there ever be a case where you would lose something for storing character data as a blob?

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    NO, BLOBS are Binary --- Whatever stored is returnd same. (equivalent to RAW data)
    Last edited by LKBrwn_DBA; 08-13-04 at 09:44.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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