Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Unhappy Unanswered: Unable to detect BLOB update

    I am not able to detect BLOB changes. This is a sample script, the trigger ti_ATestImage_Upd is not raised, column timestamp is not updated.


    if object_id('ATestImage') is not null drop table ATestImage
    GO
    create table ATestImage(X image,TS timestamp not null)
    GO
    create trigger ti_ATestImage_Upd on ATestImage for update as
    begin
    select 'Upd'
    end
    GO
    insert ATestImage(X) values ('sample')
    GO
    declare @Pointer varbinary(16)
    select @Pointer=textptr(X) from ATestImage
    select cast(cast(X as binary(50)) as char(50)),TS from ATestImage
    UPDATETEXT ATestImage.X @Pointer 0 0 WITH LOG 'add this '
    select cast(cast(X as binary(50)) as char(50)),TS from ATestImage

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Bol states that writetext doesn't fire triggers.
    I can't find any mention of the same for updatetext but it doesn't surprise me.

    'The with log' is ignored in v2000

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Nigel,
    thanks for replying to my post. There must be a way how to detect it, but it is undocumented. Full Text Indexing detects changes of BLOBs, it is running many undocumented DBCC commands.
    It not a real problem for me now. In our database we don't use more than 200 small static pictures in BLOBs and users read them once for a session, it is not a performance problem.
    I just created a replacement of FTI able to search for more than prefixes only and I wanted to populate it online. Well, it will be full populated offline, if I use it on BLOBs

    I am very surprised the row timestamp is not updated. It means BLOB cannot store an audited information, it is not secure. Bad news for FBI and good news for CIA, but what kind of news is it for me?

    Best regards, Ivo Spaleny.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> Full Text Indexing detects changes of BLOBs.

    Odd as the incrememntal works off timestamps - don't see how it can work if it doesn't affect the timestamp. Maybe they get missed.

    An option is to always update a flag (counter?) when you update the blob.

    I've always thought of bobls as un-audited. Just a means of using the database as I file store.
    I didn't realise that you could include them in a full text index.

    Bol talks about image but not text. Does that mean you can't FTI a text column or that it considers it a character field?

    Must test this soemtime.

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Nigel,
    It is in BOL "Planning for Merge Replication", MS knows it. I must update SET A=A, no change or an index page splitting, but the timestamp is updated.

    And for information about BLOB FTI, try this. It is a little bit chaotic...
    http://www.experts-exchange.com/Data..._20566439.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
  •