Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Update Columns dependency over other column on same table Expand / Collapse
Author
Message
Posted Sunday, October 6, 2013 6:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
Sean Pearce (10/4/2013)
Dwain, that constraint won't allow you to disable editing.

UPDATE #usTab1
SET Col2 = 1
WHERE Col1 = 1;
GO



Actually Sean, I never said it would. The OP wanted to disable changing Col3 based on the value of Col2. The CONSTRAINT I provided seems to do that.

Having said that, I consider this sort of a "stupid SQL DDL trick" and I'm not sure I'd ever use it myself. A trigger is probably the better way to do it because of the additional control it provides.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1501973
Posted Monday, October 7, 2013 10:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 833, Visits: 2,689
dwain.c (10/6/2013)
Sean Pearce (10/4/2013)
Dwain, that constraint won't allow you to disable editing.

UPDATE #usTab1
SET Col2 = 1
WHERE Col1 = 1;
GO



Actually Sean, I never said it would. The OP wanted to disable changing Col3 based on the value of Col2. The CONSTRAINT I provided seems to do that.

Having said that, I consider this sort of a "stupid SQL DDL trick" and I'm not sure I'd ever use it myself. A trigger is probably the better way to do it because of the additional control it provides.

Dwain, I meant no offence but simply wanted to point out to the OP that the constraint wouldn't allow editing Col2.

Having said that, I like your "stupid SQL DDL trick" and thought it was quite clever. I would also use a trigger, it is definitely the better way to implement this type of constraint.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1502268
Posted Monday, October 7, 2013 5:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
Sean Pearce (10/7/2013)
Dwain, I meant no offence ...


No problem sir and none taken.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1502430
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse