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 |
|
guizonatto
Starting Member
1 Post |
 Posted - 2010-09-09 : 17:12:35
|
| Dear all,I am stuck in a problem which I never have been before and I would like to know if you already have had the same problem and how you solved it.I need to update a field in the same table when a user insert or update a field. This field (lets call trigger_field) needs to be update only when a insertion is being made or if one specific field (lets call main_field) is updated.Well right now I don't have man hour to update the software and change bussiness rules and I need to change solve using only database stuff.The catch is that when main_field is update or insert, I need to update trigger_field with a sequential number that cannot be repeated starting with P91 followed by 9 numbers (sequential number). I store the last number in another table (only the next number to be used - example: if the last one used was P91000000009, I will have 10 stored).I tried using triggers, but when I did a bulk insertion I had to iterate (that was the only solution I have found up to know) row by row. The update and insertion took me a long time, so it is not a good solution to iterate inside a trigger (besides the tables are locked until the operation is done and this is not a desired feature). Another solution that I just came up was I would fill the trigger field using a JOB. I would need to run the job every minute, to make sure that I have all the fields are filled before the user use the fields (not a desired feature too). That could cause some errors in the application and could lock me the registers too.Well, I am here to check if you guys can help me out, because I am not very experiencied in database issues.Best regards,Guilherme.CREATE TABLE A() |
|
|
Ancy
Starting Member
23 Posts |
Posted - 2010-09-13 : 02:28:33
|
| While doing bulk insert , first create a temp table with an identity column and fill all the values to be inserted into the temp table. Now while inserting the values from the mail tabe to the temp table, for the incremental field use the following logicGet the value of the last stored number into a variable @varLet the identify column of temptable be varidinsert into the main tableselect @var + varid , other columns from temp table |
 |
|
|
|
|
|
|
|
TRIGGER TO FILL FIELD IN THE SAME TABLE