Reseed the identity Column with Trigger.

I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.

Thanks for trying.I can't count the number of DBAs and developers who have sworn off triggers, declaring them to be useless and dangerous and outlawing them from any database application.
And why?
Because people do such ridiculous things with them.
Used wisely, triggers are powerful, useful, and reliable.
I do not think you are using them wisely.
Now, instead of explaining the steps you are going through to get from Point A to Point B, tell us what Point A and Point B ARE, and maybe we can help you choose a better path. What you have described so far is definitely from the Rube Goldberg school of database development.|||1. Newly built ASP.Net menu pages, which were required to be built in a matter of weeks, read properly normalized tblUserModules table to determine which users have rights to which modules.

2. Old ASP user registration application and VB 5 RDS exe User Security tools assign users the needed rights using poorly constructed flat file style users table.

3. User registration which was deployed this February after 3 years of construction would take months if not years to convert. User Security tool in process of being converted to web based .Net tool to comply with DOD NMCI requirements but completion date on that is still months away.

4. Problem: Once new menus are deployed, which was supposed to happen 10/11/2005, then new menu will not see any updates to user rights or any new users unless SQL Server illiterates manually open Enterprise manager and add or update records in tblUserModules. In addition, once New Web based user management tool has been deployed, User Registration will not see any updates that have been made to user rights.

5. Solution: Place triggers on the two tables such that when update on one occurs the same update takes place on the other.

This would allow us to:

1. Deploy the new menu pages and later the new user management tool without waiting the months that would be required to update user registration.

2. Begin the process of migrating this site away from the technologies no longer supported by Microsoft such as Design Time Controls, which are used extensively throughout the 2500 pages of this site.

3. Capture information on user updates with recoding the old exe which is no longer permitted by DOD's NMCI policy.

If you know a way to accomplish this that would not require recoding of the exe and delaying deployment until registration has been converted over and that will not delay our deployment any more than a few days I would be glad to hear it.|||None of your systems should be updating the tables directly. They should be updating the tables by calling stored procedures. That's just database design best-practices.
If your business rules were simple I'd say put them in triggers on your tables, but since they are complicated and involve the creation of temporary objects and independent key structures I would encourage you to place this logic in the store procedures used to maintain the data.|||As stated we are in the process of updating the 2500 hundred page web site. None of the systems should be updating tables directly but they do. That what I inherited and thats what has to be lived with until changed. There should be no flat files masquerading as database tables, but there are. Thats what I have to live with until changed. Meanwhile, the site has to continue to operate in order for the WarFighter to get the parts needed to save their lives in case of aircraft malfunction. The older it gets the more malfunctions occur. It is not possible to leave the old site up untouched until all the desirable changes have been developed and tested. In real world situations involving lives of DOD personell, it is only possible to make the updates a section at a time, which means that the old breaking parts must be maintained while the replacements are being developed. I am well aware that neither the inherited databases nor the code structure is optimum or even good. What was asked is how to put baling wire and scotch tape on the system to hold it together while it is being rebuilt.
If you have answers that can address this immediate must do now problem please let me know. I have no time now for "it should have beens".

ERJ
Senior Programmer/Analsyt
MCSD MCDBA|||What you are attempting is probably achievable, but has too many hacks in it to be solved in a few forum postings, or without hours and hours of coding and debugging time. If you insist on doing this (why is there always time to do things wrong?), then I suggest you find and pay a very good SQL consult to write your code. I'm not trying to offend you, but I and other people on this forum are reluctant to spend hours of unpaid time helping people write bad code.|||Fine
I will be sure to tell that to the families of the Marines that die in Iraq because of lack of air support because you were too snobby to dirty you hands to help to provide an immediate temporary fix to a massive poorly designed production system.|||Joeller ... stop being an a**-hole. Blindman told you "... [it] has too many hacks in it to be solved in a few forum postings, or without hours and hours of coding and debugging time ...". This is a truism. People (including DBAs and database developers) expect to be paid for hours and hours (or days and days) of fixing other peoples problems.

Now I see the MCDBA below your initials in your last posting. That means you took and passed the application DBA test too. So stop being so self righteous and trying to shift the blame to a forum instead of designing a workaround or paying for the work to be done.

You suggested a plan-of-attack in your first post to this thread. You were informed that this is not in keeping with "best practices". Don't kill the messenger. You may not like the message, but it is not preventing YOU from fixing the problem, or paying for help to do so. You can even fix it poorly so it will have to be fixed again. You are in charge of your project. Piss poor planing by somebody else does not create an emergency on my part. Nor does it give you the right to try and dump blame on people whom you ask for help when they tell you something you don't want to hear!

I support the US military ... the best fighting force in the world! I spent my time in the Air Force. I worked as a civilian in DP at a Naval shipyard while I got my BA degree. I'm private sector now. You do your job, and we will do ours.

Get to work and stop bitching!|||hi Joeller,

in post #1 of the thread it is mentioned that "The id column of the temporary table will be added to the max value in the final table to create the record identifier". ..and aftr data insertion, u r trying to reseed the identity value of the "temp" table. if the identity increment is 1, and the only purpose of reseeding the identity value, is to make the new "record identifier",the previous one +1, can't u change the code in the 'trigger' to some thing like

get max(recordidentifier) from inserted, say x
add 1 to x
INSERT INTO FINALTABLE SELECT @.X, i.field1,i.field2 from inserted i

i doubt whether something is wrong in my understanding of ur post.

pl discuss|||Fine
I will be sure to tell that to the families of the Marines that die in Iraq because of lack of air support because you were too snobby to dirty you hands to help to provide an immediate temporary fix to a massive poorly designed production system.
I cannot in good conscience allow patriotic Americans to be slaughtered because I helped you write crappy, buggy software. And that will be my testimony before congress, so help me God.