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

Calculated column Only for Insert Expand / Collapse
Author
Message
Posted Friday, July 20, 2012 12:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 63, Visits: 293
I have a table with two columns created date and mofified date. I have a condition like

1. When the operation in insert then Created date is set as getdate. When update happens then created date is not chnaged.

2. When the operation is update then modiifed date as getdate. WHen insertion happens modified date is null.

Can I achive it by using calculated field column.

Note :- Trigger is not applicable here
Post #1332707
Posted Friday, July 20, 2012 12:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
I think you are talking about the computed column in SQL Server

You cannot achieve the desired result using computed columns
If trigger is not applicable, then there is only one option
Step 1. Define the column Created Date with a default value of GETDATE() or make sure you insert the value for the column in every INSERT query in your application
Step 2. Define the column Modified Date as NULLable and update it manually in every UPDATE query in your application



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1332710
Posted Friday, July 20, 2012 2:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,945, Visits: 2,909
We use CURRENT_TIMESTAMP, not getdate() now. Keeping audit data in the table itself is illegal as well as silly. What happens to the audit trail when the row is deleted?

You might want to a history table; if so, please tell us.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1333234
Posted Friday, July 20, 2012 2:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 2,033, Visits: 3,041
Sony Francis @EY (7/20/2012)
...
2. When the operation is update then modiifed date as getdate. WHen insertion happens modified date is null.
...
Note :- Trigger is not applicable here



If you don't use a trigger, you can't trust the value in the modified date -- UPDATEr might have forgotten to set it ... or deliberately set it to the wrong date.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1333242
Posted Sunday, July 22, 2012 7:44 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: Today @ 6:20 AM
Points: 3,627, Visits: 5,269
Kingston's solution is true enough as it goes, but you can also assign a default constraint to the update date column and then use this whenever a row is updated.

As a demonstration:

create table #t1 (Invoice_no int, item VARCHAR(5), price MONEY
,Inserted_date DATETIME DEFAULT GETDATE()
,Updated_date DATETIME DEFAULT GETDATE())

insert into #t1 (invoice_no, item, price)
select 102,'#1', 6.21
union all select 102,'#2', 3.56
union all select 102,'#3', 4.28
union all select 105,'#4', 1.90
union all select 105,'#5', 3.66
union all select 107,'#6', 2.01

SELECT Invoice_no, item, price, Inserted_date, updated_date
FROM #T1

UPDATE #T1
SET price = 5.00
,updated_date = DEFAULT
WHERE invoice_no = 107

SELECT Invoice_no, item, price, Inserted_date, updated_date
FROM #T1

DROP TABLE #T1


You would of course, just need to remember to do so, or force it to happen in a trigger.



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 #1333573
Posted Monday, July 23, 2012 2:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:20 AM
Points: 411, Visits: 1,400
Then while you're at it, don't use getdate() for the timestamp on your audit data and don't use current_timestamp either. Instead I suggest to use getutcdate().

Using getutcdate() ensures that a) your audit trail won't be broken when daylight saving time changes (UTC does not have day light saving). And b) when the data is moved from one timezone into another -for example by replicating the data into a server on another continent- the audit information will be unambigous.




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1333637
Posted Monday, July 23, 2012 2:33 AM


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: Today @ 6:20 AM
Points: 3,627, Visits: 5,269
R.P.Rozema (7/23/2012)
Then while you're at it, don't use getdate() for the timestamp on your audit data and don't use current_timestamp either. Instead I suggest to use getutcdate().

Using getutcdate() ensures that a) your audit trail won't be broken when daylight saving time changes (UTC does not have day light saving). And b) when the data is moved from one timezone into another -for example by replicating the data into a server on another continent- the audit information will be unambigous.


Good point RP!

Too many of us operate in our own little worlds (call them time zones if you prefer). In Thailand, we only have one time zone and no daylight savings time. So I'm safe from all but the pundits.



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 #1333640
Posted Monday, July 23, 2012 5:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
CELKO (7/20/2012)
We use CURRENT_TIMESTAMP, not getdate() now.


Heh... no... some of us don't believe in the myth of portability. Actually, I think both functions are silly because they're named wrong.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1334142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse