Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Unanswered: SQL Server 2005 Trigger or stored procedure

    I need to create either a trigger or stored procedure in SQL server 2005(hopefully someone can tell me).. Here is what I need to happen: I have a table with orders that are generated from a website. After the transaction is completed, I need have the record that was just created also copy to another table. There is a field called flag and the values in this field are either 1 or 2. Imediatly after the transaction occurs, I need the records where flag = 1 to copy to this other table. How would I go about doing this?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Do you know the value of "Flag" coming into this process?

    If so, use a proc and have it do another insert into your second table where the "Flag" = 1. If you don't know the value of "Flag" until the rows are inserted into the first table, then I would use a trigger to gather all the rows with 1 from the inserted table for table 1 and populate the second table with the data.

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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I'm going to go ahead and question why you need to shift this data about in the first place..?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try googling auditing tables and techniques in SQL Server.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh, audit, that's a justifiable answer
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Posts
    2

    Reason that I need this to happen

    The scenario I described below is just an example of what I need to do I just need the logic to do it. The real scenario is really complex, but the main reason that I need to do this is because a need a freeze frame of the data at this point in time before another transation is made.

    Thanks everyone I'll give it a shot.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're on SQL 2005, take a look at database snapshots; depending on your requirements they may be exactly what you need
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I would stick to a stored procedure solution. Taking a database snapshot after each order sounds kinda heavy
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This design sounds awfully risky for concurrent use to me... I'd expect it to go up in flames on a regular basis as you get material numbers of concurrent users.

    If you do this, be sure that your stored procedure or trigger ONLY affects the rows that you intend it to, in other words that it gets a list of PKs (Primary Keys) somehow and only affects the rows with those PKs.

    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry guys, but this looks like a pretty simple and appropriate implementation of an auditing trigger to me.
    What are your concerns?
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's a fair bit of ambiguity in the problem specification, and that always leads me directly to trouble! In the OP's (original poster's) defense, it is hard to be specific when you don't know exactly what you want.

    If we interpreted the spec correctly, and this is implemented as a trigger, and the trigger references the INSERTED pseudo-table to affect only the rows that were affected by the DML (Data Manipulation Language) statement that caused the trigger to fire, then things are probably fine. There are a lot of assumptions in that statement, and I think we all know what happens when you make assumptions...

    I don't give a rat's patoot what the problem is or how the problem gets solved. I just wanted to be sure that we solve the OP's problem in a reasonable way, instead of solving our interpretation of an interesting problem in ways that satisfy our curiousity, etc. In other words, I want to be sure that I understand that the user wants a green flyswatter then help them get one, instead of just handing them a bazooka and calling the problem solved.

    -PatP

Posting Permissions

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