Update Column Value using After INSERT Trigger on table with no Primary Key

  • Hi Guys,

    I have a table with no Primary Key. I have a column that is null. But I want to set it to 'NR' after it has been inserted. I've only seen examples doing this on tables with a Primary Key. How can I achieve this with my table. please help

  • Obviously, you're going to get a whole lot of people that will tell you that you absolutely must have some way to uniquely identify each row, PK or not.

    That notwithstanding, you can add a default to the column in question.

    You really do need to find some way to uniquely identify each row, though.

    --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)

  • Hi Jeff,

    thanks for your help. The reason I don't have a primary key is because I just dump data from another database into ours so we can then import that data. I guess I will add the default value for now. 😉

  • Understood and thanks for the feedback.

    Does the original DB have a PK or unique index on the data? If so, you could always target that column or columns as a "uniquifier" moving forward for other processes.

    As a side bar, if it is just a "data dump" as you suggest, a default may not actually work because the data being moved does have a value assigned to it even if that value is NULL. Defaults only work when the column isn't actually inserted to.

    --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)

  • hi again,

    even the original db has no pk:( what other options do I have?

  • niteshrajgopal (3/2/2011)


    hi again,

    even the original db has no pk:( what other options do I have?

    1. Add IDENTITY column to the target table?

    2. Forget trigger and update your column after whole table is populated?

    --Vadim R.

  • niteshrajgopal (2/27/2011)


    Hi Guys,

    I have a table with no Primary Key. I have a column that is null. But I want to set it to 'NR' after it has been inserted. I've only seen examples doing this on tables with a Primary Key. How can I achieve this with my table. please help

    Either I'm confused or I think we're overthinking this...

    Can't you simply run a statement like this after your insert?

    UPDATE table

    SET ColA = 'NR'

    WHERE ColA IS NULL


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • ^^ That is exactly what I thought but OP for some reason wanted to do it in the trigger.

    --Vadim R.

  • rVadim (3/4/2011)


    ^^ That is exactly what I thought but OP for some reason wanted to do it in the trigger.

    Yeah, I noticed that after I posted (I had the window open a while), but I still think it's over-thinking it, especially for bulk processing on a staging table. If he was only looking for the 'new' rows, I could see it... and I'd recommend switching from an AFTER trigger in that case to an INSTEAD OF trigger, and wrapping the inbound column in a CASE statement (create your own default).


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 9 posts - 1 through 8 (of 8 total)

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