Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    7

    Unanswered: how to insert records from tiggers at batch update process

    I want to split the records from table1 and insert the splited records to table2. The trigger is working fine when i am trying to update single record. but the trigger is not working properly at the time of batch update. At the time of batch update it is split the last record from the query and insert that into table2 instead of inserting all the records.

    Pls help me..........

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I suspect you wrote your trigger to work with one record at a time. I say this because of your comment of the trigger working for a one record update and the LAST record in a batch. Take a look at your trigger and ask yourself "What happens if I have three records to deal with in either the inserted OR deleted temp tables?"

    Post your trigger and maybe we can offer some suggestions.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2002
    Posts
    7
    Hi

    This is the trigger i have used to split the records for various conditions from table1 and insert the splitted records into another table table2

    The Problem i'm facing is if it is single update(if only one row is affected) the trigger is working fine,
    but not for batch update.


    if you are unable to follow this trigger pls give your own batch update example, we will try to implement that.

    pls help me.





    CREATE trigger udt_Break_Split
    ON Hours_tmp
    /*
    this trigger is used to do the break split in the table hours_tmp
    and insert the records into the table selnmuamid

    */

    AFTER UPDATE

    AS
    Declare @Id int
    Declare @Res_id nvarchar(8)
    Declare @DTime smalldatetime
    Declare @StartTime char(5)
    Declare @EndTime char(5)
    Declare @Pauze char(5)
    Declare @Hourworked nvarchar(5)
    Declare @Status char(1)
    --Declare @PPayroll bit
    Declare @Project nvarchar(10)
    Declare @Naam char(50)
    Declare @Type char(5)
    Declare @Hryear nvarchar(4)
    Declare @Totmin char(5)
    Declare @Endtimebr char(5)
    Declare @Stime char(5)
    Declare @Start char(5)
    Declare @Processed char(1)
    Declare @del_DTime smalldatetime
    Declare @del_StartTime char(5)
    Declare @del_EndTime char(5)
    Declare @Del_id int
    Declare @Totrows int


    --update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

    Select @Id=Id,
    @Res_id=Res_id,
    @DTime=[Datetime],
    @Start=Starttime,
    @EndTime=Endtime,
    @Pauze=Pauze,
    @Hourworked=Hoursworked,
    @Status=Status,
    @Project=Project,
    @Naam=Naam,
    @Type=Type,
    @Hryear=Hryear,
    @Processed=Processed
    From inserted -- i ,Hours_tmp h where i.[Id] = h.[id]


    Select @del_DTime=[DateTime],
    @del_Starttime=Starttime,
    @del_endTime=Endtime ,
    @del_id=id
    From Deleted

    Delete From Selnmuamid
    Where Res_id=@Res_id
    and Datetime=@del_DTime
    and Starttime >=@del_starttime
    and Endtime <=@del_endtime


    -- This is To Get total hours (hourworked column in the table ) in minutes

    Select @Totmin=Sum(Convert(Integer,(Left(@hourworked,Char index('.',@hourworked)-1) *
    60)) +Convert(Integer,(Right(@hourworked,Len(@hourworke d) -
    Charindex('.',@hourworked))))),@Endtimebr=Sum(Conv ert(Integer,(Left(@Endtime,Charindex(':',@Endtime)-1) *
    60)) + Convert(Integer,(Right(@Endtime,Len(@Endtime) -
    Charindex(':',@Endtime))))),@Stime=Sum(Convert(Int eger,(Left(@Start,Charindex(':',@Start)-1) * 60)) +
    Convert(Integer,(Right(@Start,Len(@Start) - Charindex(':',@Start)))))

    --Values Passed as Parameter to Stored procedure Break_Split

    Exec Break_Split @Id,@Res_id,@DTime,@Start,@EndTime,@Pauze,
    @Hourworked,@Status,@Project,@Naam,@Type,
    @Hryear,@Totmin,@Endtimebr,@STime,@Processed

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    basically I just wrapped your code in a while loop to step through each record in the inserted temp table. Then I moveded the delete Selnmuamid to the end.


    This has NOT been tested!


    Code:
    --------------------------------------------------------------------------------------------------

    CREATE trigger udt_Break_Split
    ON Hours_tmp
    /*
    this trigger is used to do the break split in the table hours_tmp
    and insert the records into the table selnmuamid

    */

    AFTER UPDATE

    AS

    -- -----------------------------------------------------
    -- One Declare is more efficient than multipule declares
    -- -----------------------------------------------------
    Declare @Id int
    , @Res_id nvarchar(8)
    , @DTime smalldatetime
    , @StartTime char(5)
    , @EndTime char(5)
    , @Pauze char(5)
    , @Hourworked nvarchar(5)
    , @Status char(1)
    -- , @PPayroll bit
    , @Project nvarchar(10)
    , @Naam char(50)
    , @Type char(5)
    , @Hryear nvarchar(4)
    , @Totmin char(5)
    , @Endtimebr char(5)
    , @Stime char(5)
    , @Start char(5)
    , @Processed char(1)
    , @del_DTime smalldatetime
    , @del_StartTime char(5)
    , @del_EndTime char(5)
    , @Del_id int
    , @Totrows int


    --update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

    select @Id = min(ID) from inserted
    while (@Id is not null) begin
    Select @Res_id = Res_id
    , @DTime = [Datetime]
    , @Start = Starttime
    , @EndTime = Endtime
    , @Pauze = Pauze
    , @Hourworked = Hoursworked
    , @Status = Status
    , @Project = Project
    , @Naam = Naam
    , @Type = Type
    , @Hryear = Hryear
    , @Processed = Processed
    -- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -- Hoursworked, Endtime and StartTime need to be converted to minutes
    -- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    , @Totmin = Convert(Integer,(Left(Hoursworked,Charindex('.',Ho ursworked)-1) * 60)) + Convert(Integer,(Right(Hoursworked,Len(Hoursworked ) - Charindex('. ',Hoursworked))))
    , @Endtimebr = Convert(Integer,(Left(Endtime, Charindex(':',Endtime)-1) * 60)) + Convert(Integer,(Right(Endtime, Len(Endtime) - Charindex(':',Endtime))))
    , @Stime = Convert(Integer,(Left(Starttime, Charindex(':',Starttime)-1) * 60)) + Convert(Integer,(Right(Starttime, Len(Starttime) - Charindex(':',Starttime)))))
    From inserted i
    where id = @id


    -- This is To Get total hours (hourworked column in the table ) in minutes
    /*
    Select @Totmin = Sum(Convert(Integer,(Left(@hourworked,Charindex('. ',@hourworked)-1) * 60)) +
    Convert(Integer,(Right(@hourworked,Len(@hourworked ) - Charindex('. ',@hourworked)))))
    , @Endtimebr = Sum(Convert(Integer,(Left(@Endtime,Charindex(':',@ Endtime)-1) * 60)) +
    Convert(Integer,(Right(@Endtime,Len(@Endtime) - Charindex(':',@Endtime)))))
    , @Stime = Sum(Convert(Integer,(Left(@Start,Charindex(':',@St art)-1) * 60)) +
    Convert(Integer,(Right(@Start,Len(@Start) - Charindex(':',@Start)))))
    */
    -- Values Passed as Parameter to Stored procedure Break_Split

    Exec Break_Split @Id,@Res_id,@DTime,@Start,@EndTime,@Pauze,@Hourwor ked,@Status,@Project,@Naam,@Type,@Hryear,@Totmin,@ Endtimebr,@STime,@Processed

    select @Id = min(ID) from inserted where id > @id
    end

    -- --------------------------------------------------------------
    -- The deleted temp table can be joined to the Selnmuamid table
    -- to deleted the unwanted records. If you need the ID from the
    -- inserted table then join the inserted table with the
    -- deleted table and then join the Selnmuamid table.
    -- This is a good example of how you can ditch the row-at-a-time
    -- mentality and start using set theory for processing records.
    -- --------------------------------------------------------------
    delete
    from Selnmuamid s
    join deleted d on s.Res_id = d.Res_id
    where [s.Datetime] = d.del_DTime
    and s.Starttime >= d.del_starttime
    and s.Endtime <= d.del_endtime

    /*
    Select @del_DTime = [DateTime]
    , @del_Starttime = Starttime
    , @del_endTime = Endtime
    , @del_id = [id]
    From Deleted
    where Id = @Id

    Delete
    From Selnmuamid
    Where Res_id = @Res_id
    and [Datetime] = @del_DTime
    and Starttime >= @del_starttime
    and Endtime <= @del_endtime
    */

    --------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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