Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2011-12-02 : 13:37:42
|
| I'm operating on a SQL 2005 server, and what I need is bascially a better implementaiton of the SQL timestamp.I want an actual datetime timestamp that operates like the SQL timestamp data type, BUT that is only updated when the value being updated is different from the current value.So basically, the time stamp would be set when any record is inserted. It would also be set on an update of any column if the value in that column is different than the current value.In other words, if you run a query that sets the value of column "amount" in all rows in the table to 0.00, the time stamp would only be set on rows that weren't already 0.00 in the first place.So, I'm not looking for someone to code this out, just a general idea of whether this is doable or not, and if so, the basic approach to use.The main issue is that I'm familiar with checking the value of a specific column in a trigger, but I'm not sure how to write a generic trigger to assess any column that might be updated in a table.Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-02 : 13:49:03
|
| create trigger xxx on tbl for updateasupdate tblset z_updated = getdate()from tbl tjoin deleted don d.pk = t.pkwhere (t.col1 <> d.col1 or (t.co11 is null and d.col1 is not null) or (t.co11 is not null and d.col1 is null))or (t.col2 <> d.col2 or (t.co12 is null and d.col2 is not null) or (t.co12 is not null and d.col2 is null))...If you like you can generate this from information_schema.columns.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2011-12-02 : 14:49:18
|
| Ahh, so there is no magic bullt. I was hoping to avoid having to manually mantain DDL modifications to the table, but this will work.ThanksP.S. also, for this to work you need to join to the insered table, not the deteled in order for it to work with both inserts and updates. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-02 : 15:05:49
|
| Nope - the inserted table holds the after values which will be the same as the table - no harm in doing it though and might be better (or not)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2011-12-02 : 15:59:49
|
| Good point, I'll have to test for insert or update first then, or should I just make a separate trigger for inert and a separate one for update?? Hmm... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-03 : 01:45:40
|
quote:
Originally posted by malachi151
Good point, I'll have to test for insert or update first then, or should I just make a separate trigger for inert and a separate one for update?? Hmm...
you can make a combined one for insert,updatebut then the logic has to be change to make it a left join instead of inner join with deleted as for inserts you wont have anything in deleted table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Time stamp trigger on update