February 18, 2009 at 9:50 am
Can I use a formula to set the default value of a field based on other fields in the record?
For example, I have three fields:
Active
ModifiedDateTime
DeactivatedDateTime
If Active is set to N and DeactivatedDateTime is Null, then I want it to default to the ModifiedDateTime. Can I use a formula or query in the Default setting of the table or do I need to use a trigger to set this?
February 18, 2009 at 10:49 am
A default is only going to apply when you insert a record - not when you update a record. So, do you regularly insert inactive records?
I think you may want a calculated field.
February 18, 2009 at 6:27 pm
Yes, this table is used as an interface table so it does have inactive records inserted, then they are moved out into another table to update an existing record.
Am I right in assuming that the calculated field would not allow a value to be inserted? If so it would be prefereable to be able to use some formula or query to set the default value of the field. Is that possible? Or do I need to use a trigger to update the fiedl after it is inserted?
February 19, 2009 at 3:14 am
Well, you can have a computed column or you can do the computing in the query itself.
CREATE TABLE #SomeTable
(
TableID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Active CHAR(1) NOT NULL,
ModifiedDateTime DATETIME NULL,
DeactivatedDateTime DATETIME NULL,
NewDeactivatedDateTime AS ( CASE WHEN Active = 'N' AND DeactivatedDateTime IS NULL THEN ModifiedDateTime ELSE DeactivatedDateTime END )
)
INSERT#SomeTable( Active, ModifiedDateTime, DeactivatedDateTime )
SELECT'Y', GETDATE(), NULL
UNION ALL
SELECT'Y', GETDATE() - 1, GETDATE()
UNION ALL
SELECT'N', GETDATE() - 2, NULL
UNION ALL
SELECT'N', GETDATE() - 3, GETDATE()
-- Method 1 (Using Additional Computed Column)
SELECT TableID, Active, ModifiedDateTime, NewDeactivatedDateTime FROM #SomeTable
-- Method 2 (Using Conditional Computing the Column)
SELECTTableID, Active, ModifiedDateTime,
( CASE WHEN Active = 'N' AND DeactivatedDateTime IS NULL THEN ModifiedDateTime ELSE DeactivatedDateTime END ) AS NewDeactivatedDateTime
FROM#SomeTable
DROP TABLE #SomeTable
--Ramesh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply