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

Question

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

niteshrajgopal

SSCommitted

Points: 1877
More actions
February 27, 2011 at 11:48 pm

#149978

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

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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)

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 2

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. 😉

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

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)

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 4

hi again,

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

rVadim Hall of Fame Points: 3969 More actions · Answer 5

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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

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

rVadim Hall of Fame Points: 3969 More actions · Answer 7

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

--Vadim R.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

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