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 |
|
doran_doran
Posting Yak Master
179 Posts |
 Posted - 2009-11-05 : 10:13:15
|
| when user wants to update a record, sp will update the old record's active field with 0 and create a new record with the updated info with active =1. for example,Table: tblNameID FName LName Active1 John Smith 1If user change the Smith to Lee then SP will mark the current record Active=0, and create another record with active=1. ID FName LName Active1 John Smith 02 John Lee 1How do I do that using Stored Proc.? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-11-05 : 10:46:29
|
| you might want to look into an update trigger on tblName or the OUTPUT clause<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
doran_doran
Posting Yak Master
179 Posts |
Posted - 2009-11-05 : 10:50:46
|
| The DBA will not allow trigger. He suggested SP to take care of the update and create new records. Basically, we need keep the original record the way it is and mark it inactive. And create new record with whatever changes user will pass. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-11-05 : 11:17:31
|
| yes I can see why, poorly designed triggers (or any sql object for that matter) could wreak havoc. Then use the OUPUT clause when doing UPDATE on that table. dump the OUPUT to a table variable or better yet a temp table (if your dba says ok, I doubt it so use local table variable) and then do an INSERT using that local table variable.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
doran_doran
Posting Yak Master
179 Posts |
Posted - 2009-11-05 : 12:17:17
|
| How about1. I create a new record and get the id (identityscope)2. I update the old record (make active = 0)3. Update the newly created record with whatever update user requested (with the identityscope i got from step 1)Will this work? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-11-05 : 12:22:48
|
| sounds Convoluted but feasible, try it and let us know. do you have a dev server?<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-05 : 12:48:23
|
quote:
Originally posted by doran_doran
How about1. I create a new record and get the id (identityscope)2. I update the old record (make active = 0)3. Update the newly created record with whatever update user requested (with the identityscope i got from step 1)Will this work?
I'm assuming that you have a natural key, so I'd do it like:1. BEGIN TRANSACTION2. Update old record to inactive3. Insert new record as active4. COMMIT TRANSACTION |
|
|
|
|
|
|
|
|
Update old and create new