Trigger Problem-Urgent

Question

Trigger Problem-Urgent

Viewing 8 posts - 16 through 22 (of 22 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

GSquared (2/9/2009)
GilaMonster (2/9/2009)
Vijaya Kadiyala (2/9/2009)
What if the table already exists, so in this case we have to use Update Statement right!!
Why?
Alter Table Add .... and add the computed column to the existing table. Since it's computed, the column doesn't actually store data and can't be updated. It's only calculated when the data is retrieved
I took his question as meaning the table already exists and there is already a column in it for that data.
Yes, the column can be dropped and rebuilt, but you have to make darn sure that there is no code trying to update that column if you're changing from data storage to calculated. That can be complex in some environments, where not all the database code is in procs.

I think that in a case that you drop a none computed column and replace it with a computed column, you can use an "instead of trigger" in order to make sure that no one will get a runtime error because he tried to update the new computed column.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 2

[font="Verdana"]It's usually not too hard to do a code search within the database and applications to find whether a column is referenced.

I agree that the use of an instead of trigger should allow any "un-caught" code to still work as expected.[/font]

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

Bruce W Cassidy (2/9/2009)
[font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀
And of course you can index calculated columns. So all in all, the calculated column is a far better solution.
[/font]

Heh... dang it... ya beat me on the "persisted" option.

Shifting gears a bit... not all Calculated Columns can be indexed... the formula for the calculated column must be deterministic in order to add an index to it.

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

You could always use a view.

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

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 5

Adi Cohn (2/9/2009)[hrI think that in a case that you drop a none computed column and replace it with a computed column, you can use an "instead of trigger" in order to make sure that no one will get a runtime error because he tried to update the new computed column.

That's a good tip.

Of course, one would have to ask, in this particular example, why the Expired flag was being updated from several places that would warrant having the trigger, but it could be very usefull for other calculated columns.

Derek

GSquared SSC Guru Points: 260824 More actions · Answer 6

Bruce W Cassidy (2/9/2009)
[font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀
And of course you can index calculated columns. So all in all, the calculated column is a far better solution.
[/font]

Since the column is calculated against getdate, there's no point in persisting it, since it will have to be recalculated at runtime every time it's queried.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

GSquared (2/10/2009)
Bruce W Cassidy (2/9/2009)
[font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀
And of course you can index calculated columns. So all in all, the calculated column is a far better solution.
[/font]
Since the column is calculated against getdate, there's no point in persisting it, since it will have to be recalculated at runtime every time it's queried.

Since the column is calculated against getdate, it cannot be persisted. For a calculated column to be persisted it must, among some other requirements, reference only deterministic, precise functions. Getdate is not deterministic.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 8

[font="Verdana"]Sigh. There's always a critic. 😛

Quite right, of course. A calculated column with getdate() in it will not persistable.[/font]