How to create trigger to update table from another table data

Question

I have a table Schedule like this

ID  |       FROM      |       TO       | day of week
1   |     12/10/2016  |    01/03/2017  |     2
2   |     10/12/2016  |    10/15/2017  |     7
3   |     10/14/2016  |    10/20/2017  |     1
4   |     01/20/2016  |    01/30/2017  |     5
5   |     11/25/2016  |    01/30/2017  |     3
6   |     11/29/2016  |    01/30/2017  |     3

I need to create a trigger: after each insert, another table (single_date) needs to be updated with the translation of the inserted row.

For example first row means: every Tuesday between 12/10/2016 and 01/01/2017

and single_date table should update with the following rows

 ID  | schedule_ID |       date      
 1   |     1       |    12/13/2016  
 2   |     1       |    12/20/2016 
 3   |     1       |    12/27/2016  
 4   |     1       |    01/03/2017

What have you tried so far? Is it the trigger you need help with or the underlying insert? — JohnHC
Possible duplicate of creating triggers for After Insert, After Update and After Delete in SQL — Cybersupernova

p2k p2k · Accepted Answer · 2017-01-03T12:43:08

Below code for After Insert trigger will help you.

Create table Maintable
(
ID int,
FROMD date,
TOD date,
day_of_week int
)

Create table trgTable
(
ID int identity(1,1),
schedule_ID int,
Insdate date 
)


CREATE TRIGGER trgAfterInsert ON Maintable
FOR INSERT
AS  
begin
    DECLARE @MinDate DATE, @MaxDate DATE, @dow int, @id int

    SELECT 
        @MinDate = FROMD, @MaxDate = TOD, @dow = day_of_week, @id = id
    FROM Inserted i

    ;With CTE AS
    (
        SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
                Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
        FROM    sys.all_objects a
                CROSS JOIN sys.all_objects b
    )
    Insert into trgTable
    Select @id, Date from CTE
    Where datepart(dw, Date) = @dow
end

Insert into Maintable values (1, '12-10-2016','01-03-2017', 2)

Select * from Maintable;
Select * from trgTable;

Output:

 ID  | schedule_ID |       insdate      
 1   |     1       |    12-13-2016  
 2   |     1       |    12-20-2016 
 3   |     1       |    12-27-2016  
 4   |     1       |    01-03-2017

How to create trigger to update table from another table data

3 Answers