| Author |
Topic |
|
raghutumma
Starting Member
11 Posts |
 Posted - 2007-10-03 : 02:23:35
|
| hi,How can i get DateTime Stamp of a Table in SQL Server???We can get Datetime Stamp of a Table after modifying Structure from Sys.Tables. But i need Date Time Stamp of a Table after Modifying the Data in Table(Like Inserting Records,Deletindg Records & Altering Records) without adding any extra columns in my table to store datetime stamps... Can we get that one??? if yes, How can i get datetime stamp???Thanx in Advance,Regards,Raghu... |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 02:38:15
|
| One solution would be to add an extra column (datetime dataype) to the table with a default value of NULL. You could then add a trigger that updated that column with current getdate() value when a row is updated or inserted.-ec |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 02:52:41
|
| i see that you edited your original post and changed the requirements a bit..If you can't add the datetime column to your table you could use a trigger still. There are special tables called inserted and deleted that are available only to triggers. You can use these tables and insert the data to a newly built audit table. this way you don't have to modify the structure of your current table and are still able to audit the data changes.I am sure that there is an article or forum thread here on doing just that. do some searching here to see what you find.-ec |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 02:55:46
|
| where can i find that special tables...??? can u send me details???Raghu |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 02:57:41
|
quote:
Originally posted by raghutumma
where can i find that special tables...??? can u send me details???Raghu
look in BOL for details on triggers. the special tables are called INSERTED and DELETED. these pseudo tables contain the rows affected by an INSERT/UPDATE or by a DELETE. books online (BOL) has all the details for you. do some searching here like i suggested and you should be able to find some code that you can adapt for your own auditing purposes.-ec |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 03:02:55
|
| hi EC,Is system automatically stores the datetime stamp of everytable???Raghu |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 03:04:45
|
quote:
Originally posted by raghutumma
hi EC,Is system automatically stores the datetime stamp of everytable???Raghu
no. but you can grab that using getdate() in your trigger and insert that value along with all the inserted/changed rows into your audit table.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 03:08:38
|
| ok... Ec, But actually what we are thinking is we have .dll , if u click on save button it will save the database. our prob is in that database after adding the records order is changing . we dont want to change that dll ,for that we wrote a Script. this script is running everytime after clicking print button. but we need is it should run when database is changed. if we have the Datetimestamp of the table problem will be solved very easily...Raghu |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 03:16:30
|
quote:
Originally posted by raghutumma
ok... Ec, But actually what we are thinking is we have .dll , if u click on save button it will save the database. our prob is in that database after adding the records order is changing . we dont want to change that dll ,for that we wrote a Script. this script is running everytime after clicking print button. but we need is it should run when database is changed. if we have the Datetimestamp of the table problem will be solved very easily...Raghu
yeah, so I don't really know what you are talking about. I'm not asking you to change a dll. I gave you a solution that will work without having to add columns to the tables being modified. -ec |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 03:20:27
|
| i am not talking about dll change. i am saying what we are doing... why we are writing script...i will go through link which u have sent...Raghu |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 03:22:13
|
| are you saying that the order of the information being displayed is changing each time an update or insert is performed? is that the problem you are trying to solve?-ec |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 03:23:18
|
| yes EC... we wrote script for that problem...Raghu |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 03:25:21
|
| but it will run always... we need that script should run when database change occurs...Raghu |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 03:26:59
|
quote:
Originally posted by raghutumma
but it will run always... we need that script should run when database change occurs...Raghu
do you use an ORDER BY clause in your SELECT statement? that is the only way to guarantee the ordering of data.-ec |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 03:29:31
|
| already i used ORDER BY Clause in my Statement...Prob is not with the script...prob is when to run the script... it will run always... but i need that script should run when records added or deleted in my table.Raghu |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-10-03 : 04:10:16
|
maybe you can tell us what that 'script' is doing. And why do you need that script to run when records are added or deleted from your table
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
raghutumma
Starting Member
11 Posts |
Posted - 2007-10-03 : 04:31:33
|
| actually we have a crystal report. we have linked stored procedure to crystal report for getting data from database. if we add or delete record the order of diplaying records is changing. so my script will update the values after insertion or deletion. by running my script, report will get data orderly. but here the prob iz my script is running always when click the button. My script should run when database changes(Additions or Deletions & Alterations in Table)... i think nw you got what the prob is...Raghu |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-03 : 04:44:54
|
Ooohhh!The dreaded "actually" was finally written...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-03 : 04:57:10
|
| Have a TRIGGER on the table that updates another table (one row, one column, lets call it "flag") whenever there is a change.have your script check if the "flag" is set.If NO then do nothing.If YES, clear the Flag, and run as normal.This is better than updating the order column every time something changes, because you may change things more often than you need the report. This will fix the order column just-in-time before the report is run.Kristen |
|
|
|
|
How can i get DateTime Stamp of a Table in SQL Ser