Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Trying to update a record after insert

    I am trying to update a record after it is inserted into a table.

    It gets inserted into the table in a batch so is not neccesarily the last record. I need to carry out a calculation on a field in the record if another field in that record matches a certain critieria.

    I thought I might be able to loop but I am struggling to do this: This is what I have so far:

    DECLARE @RowCount INT
    SET @RowCount = 49804
    -- Declare an iterator
    DECLARE @I INT
    -- Initialize the iterator
    SET @I = 49800

    -- Loop through the rows of a table @myTable
    WHILE (@I <= @RowCount)
    BEGIN
    -- Declare variables to hold the data which we get after looping each record
    DECLARE @itid VARCHAR(50), @iRecordType VARCHAR(50), @ijoinTime VARCHAR(50)

    -- Get the data from table and set to variables
    SELECT @itid = TransactionID, @iRecordType = TypeRecord, @iStartTime = StartTime FROM CDR_Transactions WHERE TransactionID = @I
    -- Display the looped data
    PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
    PRINT 'Tran ID = ' + @itid + ', Record Type = ' + @iRecordType + ' StartTime = '+ @iEmail
    -- Increment the iterator
    SET @I = @I + 1
    END

    However when doing this I end up with some lines repeated. It is possible that some transactions have been deleted so the there could be gaps in the sequence.

    or if there is another way of doing please advise.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What exactly are you trying to accomplish? If all you need to do is update some rows that meet a specific criteria, then use an UPDATE statement with an appropriate WHERE clause to limit the rows that are updated. This will probably be several hundred times faster than trying to iterate through the rows in the table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I dont think I can do that because the update I am trying to do is calculation. So I needto update a field in that record based other fields in that record. So there WHERE clause has to know which record it is updating.

    Sample of table below:
    TransID Category StartTime EndTime LengthofTime
    1 A 22/05/2011 08:00:54 22/05/2011 08:08:53 00:07:59
    2 B 22/05/2011 07:59:46 22/05/2011 08:08:51 00:00:00
    3 B 22/05/2011 08:00:52 22/05/2011 08:08:51 00:00:00
    4 A 22/05/2011 07:56:16 22/05/2011 08:09:14 00:12:58
    5 B 22/05/2011 08:05:17 22/05/2011 08:13:05 00:00:00

    Category B does not have the LengthofTime from the source data so I need to calculate this and the length is going to vary for each record.

    So I need to identify the TransID to then get the EndTime and the StartTime for that specific record and then carry out the calculation.

    I may have over complicated this as I have been going round in circles so if there is an easy way please let me know.

    This will be run from a trigger after Insert.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by HelpMePlease View Post
    This will be run from a trigger after Insert.
    This solves your problem by doing an update after insert. There is no need to use loops or cursors in a trigger. But the reasoning for using a trigger should be for referal integrity or applying a business rule.

    I don't see why the l_o_t column can't be included with the insert sproc? Would seem a lot easier.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I dont see how this helps me.

    If I do a calculation on the field how does it know which record it is referring to? And when I tested it that way it took the endtime and starttime from the last record inserted and applied the result to all records that were a category B.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    The record inserted is the one you are doing calc on. The trigger handles one record at a time as inserted, updated, or deleted.

  7. #7
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    ok - Let me go and re-test this. Will return soon with results.
    Thanks

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Post trigger sql, ok? Ps. The from table is inserted and not CDR_Transactions. Select something from inserted.

  9. #9
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    That works to update the records I need to update.

    Many thanks.

    This gives rise to another problem with formatting on the field I am calculating, I will start another thread thou.
    Cheers

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by corncrowe View Post
    The record inserted is the one you are doing calc on. The trigger handles one record at a time as inserted, updated, or deleted.
    Triggers do NOT handle one records at a time. Triggers fire once for each transaction, which may involve multiple records. If you write your trigger with the assumption that only one record is involved in the transaction, it is only a matter of time before you get corrupted data in your database.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    Triggers do NOT handle one records at a time. Triggers fire once for each transaction, which may involve multiple records. If you write your trigger with the assumption that only one record is involved in the transaction, it is only a matter of time before you get corrupted data in your database.
    My bad. I was thinking the inserts were single row. But as I said, he didn't need to do loops or cursors because the inserted record was the one that he could do calc on. Besides, I also mentioned that he could write the calc in the insert sproc which would probably be a better place.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I disagree there too, though.
    This is a data rule, and so it belongs as close to the data as possible. That means a trigger, or perhaps better in this instance, a computed column. Computed Columns
    If you use a sproc, then you have to be able to ensure that no other process adds or updates that data, except through the sproc. Difficult to enforce.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    I disagree there too, though.
    This is a data rule, and so it belongs as close to the data as possible. That means a trigger, or perhaps better in this instance, a computed column. Computed Columns
    If you use a sproc, then you have to be able to ensure that no other process adds or updates that data, except through the sproc. Difficult to enforce.
    The sproc performing the insert will populate col1 and col2 which is used as the basis for calc column. Why use a trigger at this point? I must be missing something here because the op wanted to do an update after insert and I didn't suggest that method as practical.

  14. #14
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I am confused as to why this is so complicated, with discussions of updates after inserts, and triggers, etc., etc., etc.

    In the only statement that is required for this whole process, the insert statement, why not:

    Code:
    insert
    into    theResultTable
    select  TransID
            ,Category
            ,StartTime
            ,EndTime
            ,case LengthOfTime
                    when '00:00:00' then datediff(interval,EndTime,StartTime)
                    else LengthOfTime
            end LengthOfTime
    from    theSourceTable
    I don't know the character of the LengthOfTime field so my reference to '00:00:00' may not be correct and the datediff function will have to be spec'd with an interval and may have to be further transformed to conform to the resulting LengthOfTime data type.

    Of course this is overly simplified, but am I missing something that this seems to be far easier than has been discussed ?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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