llerdal llerdal - 3 months ago 16
SQL Question

SQL Server trigger to add future records to table

I would have to say I am very new to SQL Server triggers but I hope some good samaritan will help me with this problem. I want to set a trigger that checks if a record has been added to a table and if so insert a series of records into a different table adding time to the timestamp in the monitored table. I have wrote what I how is easy to understand pseudo-code that will help in the creation of these triggers.

IF there is a new record in the

tBatch
table with a specific batch ID, then insert a record into
DeSchedule
.

Query to find new records in
tBatch
and pull data from
tBatch
:

SELECT
b.BatchID, b.fermNumber, b.BatchStartTime, b.PropStartTime, b.SiteID
FROM
[DDIApplication].[dbo].[tBatch] b
INNER JOIN
[DDIApplication].[dbo].[tBatchDetails] bd ON b.id = bd.BatchDetailID


Trigger

IF there is a 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 into DeSchedule(DATEADD(hour, 6, PropStartTime), 6666, 1, YP6, , , )

insert into DeSchedule(DATEADD(hour, 10, PropStartTime), 6666, 1, YPD, , , )

insert into DeSchedule(DATEADD(hour, 10, BatchStartTime), 6666, 1, 10, , , )

insert into DeSchedule(DATEADD(hour, 20, BatchStartTime), 6666, 1, 20, , , )

insert into DeSchedule(DATEADD(hour, 30, BatchStartTime), 6666, 1, 30, , , )

insert into DeSchedule(DATEADD(hour, 40, BatchStartTime), 6666, 1, 40, , , )

insert into DeSchedule(DATEADD(hour, 50, BatchStartTime), 6666, 1, 50, , , )

insert into DeSchedule(DATEADD(hour, 65, BatchStartTime), 6666, 1, DROP, , , )

insert into DeSchedule(DATEADD(hour, 70, BatchStartTime), 6666, 1, BW, , , )


Additional trigger

When records are entered into
tBatchDetails
, check against
DeSchedule
to try and match (BatchID, SampleAge, SiteID)

If record is found update record and insert (Ethanol, Glucose and check the SampleCompleted Bit) from the
tBatchDetails
Table

Query for
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 all the inserts in the trigger is there a way to loop through the SampleAge Table and Insert a record for each SampleAge.

SampleAge Table schema

| SiteID | SampleAge | ScheduleTime |
| 1 | YP6 | 6 |


So instead of:

insert into DeSchedule ([SiteID],[PreTimeStamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted])
select SiteID, DATEADD(hour, 4, PropStartTime), BatchID, FirmNum, '4Hrs', NULL , NULL ,NULL
from INSERTED


It would get the amount of hours for the dateadd function from ScheduleTime and the SampleAge from the SampleAge column of the SampleAge table. Then it
will loop through the sample age table and insert a record for each sampleage that has the same SiteID.

Answer

Here is the trigger for tBatch table (adapt the insert into code to insert exactly what you need):

USE [test] --Change by your database Name
GO

CREATE TRIGGER Trigger_Insert_into_DeSchedule ON [dbo].[tBatch] 
AFTER INSERT 
AS 

BEGIN
insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 6, PropStartTime), BatchID, 6666, 1, 'YP6', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, 'YPD', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, '10', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, '20', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, '30', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, '40', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, '50', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, 'DROP', NULL , NULL  from INSERTED

insert into DeSchedule ([SiteID],[Timestamp],[BatchID],[FermNumber],[SampleAge],[Ethanol],[Glucose],[SampleCompleted]) 
select SiteID, DATEADD(hour, 10, PropStartTime), BatchID, 6666, 1, 'BW', NULL , NULL  from INSERTED

END

GO

And the trigger for tBatchDetails table :

USE [test] --Change by your database Name
GO

CREATE TRIGGER Trigger_update_details_DeSchedule ON [dbo].[tBatchDetails]
    AFTER INSERT 
AS 


BEGIN
    DECLARE @BatchId int, @Ethanol varchar(10), @Glucose varchar(10), @SampleAge varchar(10);
    SELECT @BatchId = [BatchTablelID],@Ethanol = [Ethanol], @Glucose= [Glucose], @SampleAge = [SampleAge]  from INSERTED

    update [dbo].[DeSchedule] SET [Ethanol] = @Ethanol, [Glucose] = @Glucose WHERE [BatchID] = @BatchId AND [SampleAge] = @SampleAge

END

GO