| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2009-10-29 : 00:17:07
|
| Hi.I guess 2 questions.1st an example of a trigger to update timestamp 2nd.Should i use triggers to check for data concurrency?Is there a better solution.I could use an sqldataadapter in my program but since i need speed i prefer to try to it on SQL. So SQL handle the update or delete and provide the exception and the data. |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-10-29 : 12:07:42
|
| i will not suggest using triggers.. you can handle it better in Stored Procedure which will address 2 Q.write a SP for insert or update and do all ur business rule check here if requied you can raise errors..========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-10-29 : 13:15:49
|
| Aha.Ok but i've read on msdn that in order to use timestamps for data concurrency, u must use triggers.Or i don't have to use timestamps?Any articles or examples?Thanks. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-29 : 14:09:20
|
| you want to check the timestamp value when you go to update. the idea of the trigger is to roll back the transaction and raise an error if the timestamp has changed since you read the data. NOTE that this is NOT a datetime type value. When the row is updated, the timestamp will change, thus if you read a row and allow the front end user to change it, when you write it back, you need to check the timestamp to make sure it is the same as it was when you performed the read.this can be done in a trigger or a stored proc. trigger ensures that ALL processes attemting to update the data are checked, whereas a stored proc can't.also, you don't update the timestamp manually, SQL does itsee here: http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-10-29 : 15:03:33
|
| As Russel has pointed out, a trigger will reflect all changes to data in the table, whereas a stored procedure will only affect changes made by calling that procedure. Additionally, the update logic would have to be included in every single stored procedure that modifies the data.For these reasons, a trigger is more appropriate for your task than including this logic in procedures.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-10-29 : 18:52:46
|
| Hi.Thanks.I see 2 problems. First the article russell gave says :The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server." so is it wise to use it?Also there is no trigger example given.Ok i should probably try to create one trigger and let you know my problems.I see that rowversion is what microsoft suggest for future use, so is it wise to use it?Thanks again. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-29 : 21:47:19
|
| yes, should use rowversion |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-10-30 : 11:24:06
|
| sapator, are you referring to a true "timestamp" datatype, which is binary data maintained by the server and looks nothing like a datetime value, or are you talking about a datetime value that you will use to keep track of when your record was modified?You do not need triggers to update the (deprecated) timestamp datatype.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-30 : 11:30:31
|
| that's what i was trying to say, but you do need to compare it before allowing the update to other fields in the table -- if you're breaking off data for the front end to modify |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-10-30 : 22:01:42
|
| Hi.Thanks again.I'm refering to the sql timestamp not the date.So if i'm correct the best use is rowversion?Does rowversion updates itself?Also i was thinking triggers because i won't have to write the code in every sp i need to check for concurrency.Is this a correct approach?Thanks |
|
|
|
|
decent trigger to update timestamp.