SQL Server trigger to add future records to table -

i have new sql server triggers hope samaritan me problem. want set trigger checks if record has been added table , if insert series of records different table adding time timestamp in monitored table. have wrote how easy understand pseudo-code in creation of these triggers.

if there new record in tbatch table specific batch id, insert record deschedule.

query find new records in tbatch , pull data tbatch:

select       b.batchid, b.fermnumber, b.batchstarttime, b.propstarttime, b.siteid      [ddiapplication].[dbo].[tbatch] b inner join      [ddiapplication].[dbo].[tbatchdetails] bd on b.id = bd.batchdetailid 

trigger

if there new record in tbatch, create these records in deschedule

deschedule schema

| id | siteid | timestamp | batchid | fermnumber | sampleage | ethanol | glucose | samplecompleted | 

create these records in deschedule:

insert deschedule(dateadd(hour, 6, propstarttime), 6666, 1, yp6, , , )  insert deschedule(dateadd(hour, 10, propstarttime), 6666, 1, ypd, , , )  insert deschedule(dateadd(hour, 10, batchstarttime), 6666, 1, 10, , , )  insert deschedule(dateadd(hour, 20, batchstarttime), 6666, 1, 20, , , )  insert deschedule(dateadd(hour, 30, batchstarttime), 6666, 1, 30, , , )  insert deschedule(dateadd(hour, 40, batchstarttime), 6666, 1, 40, , , )  insert deschedule(dateadd(hour, 50, batchstarttime), 6666, 1, 50, , , )   insert deschedule(dateadd(hour, 65, batchstarttime), 6666, 1, drop, , , )  insert deschedule(dateadd(hour, 70, batchstarttime), 6666, 1, bw, , , ) 

additional trigger

when records entered tbatchdetails, check against deschedule try , match (batchid, sampleage, siteid)

if record found update record , insert (ethanol, glucose , check samplecompleted bit) tbatchdetails table

query batchdetailid:

select     [batchdetailid], [id],    [sampletimestamp],    [sampleage], [glucose], [ethanol] 

from [ddiapplication].[dbo].[tbatchdetails]

tbatchdetails schema

| id | timestamp | batchtableid | sampleage | batchid | ethanol | glucose | 

edited

instead of writing out inserts in trigger there way loop through sampleage table , insert record each sampleage.

sampleage table schema

| siteid | sampleage | scheduletime | |   1    |    yp6    |      6       | 

so instead of:

insert deschedule ([siteid],[pretimestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 4, propstarttime), batchid, firmnum, '4hrs', null , null ,null   inserted  

it amount of hours dateadd function scheduletime , sampleage sampleage column of sampleage table. loop through sample age table , insert record each sampleage has same siteid.

here trigger tbatch table (adapt insert code insert need):

use [test] --change database name go  create trigger trigger_insert_into_deschedule on [dbo].[tbatch]  after insert    begin insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 6, propstarttime), batchid, 6666, 1, 'yp6', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, 'ypd', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, '10', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, '20', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, '30', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, '40', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, '50', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, 'drop', null , null  inserted  insert deschedule ([siteid],[timestamp],[batchid],[fermnumber],[sampleage],[ethanol],[glucose],[samplecompleted])  select siteid, dateadd(hour, 10, propstarttime), batchid, 6666, 1, 'bw', null , null  inserted  end  go 

and trigger tbatchdetails table :

use [test] --change database name go  create trigger trigger_update_details_deschedule on [dbo].[tbatchdetails]     after insert     begin     declare @batchid int, @ethanol varchar(10), @glucose varchar(10), @sampleage varchar(10);     select @batchid = [batchtablelid],@ethanol = [ethanol], @glucose= [glucose], @sampleage = [sampleage]  inserted      update [dbo].[deschedule] set [ethanol] = @ethanol, [glucose] = @glucose [batchid] = @batchid , [sampleage] = @sampleage  end  go