Post reply

Find out which table columns were inserted from within a trigger

  • November 18, 2008 at 6:07 am

    #899853

    Just remember that you will get an advisory about total row length exceeding 8060 bytes if you use more than a very small handful of columns in the same table as the SQL_Variant.

    Hmmm... if you put a constraint on the column in question to allow ONLY numeric digits and the decimal point, maybe converting the column to VARCHAR would, in fact, be the best way to go and still meet the requirements of your trigger.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 18, 2008 at 6:15 am

    #899859

    Hi Jeff,

    I tried some test INSERTs this morning, on a table with 20 SQL_VARIANT columns and 17 correctly-typed columns, and didn't get any warnings about column size. I'm in the middle of changing the pricing engine code to handle the new methodology and will do some real stress testing after that.

    I may give the VARCHAR method a go if I have time - luckily the pricing engine will use the exact same logic (i.e. that a zero-length string denotes an implicit NULL and therefore no value written by the price feed) as it does now with SQL_VARIANT so will just be a case of rebuilding the table using VARCHAR and with the new constraints. It's certainly worth a try.

    Thanks for all your ideas and prompt responses.

    Grant

  • May 6, 2010 at 5:55 am

    #1163089

    Hi All,

    I am having the exact same problem now (2010).. Is there a solution from Microsoft in the meantime? Or am I stuck with ''strange default' values to see if an implicit or explicit NULL was inserted?

    best regards and really thanks in advance

    Marcel

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply