Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30

    Why use triggers

    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30
    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.

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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!

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    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
    Cheers....

    baburajv

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Joeller
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •