T-SQL Trigger

[Mosser]

So I am trying to create a trigger to make a bunch of records in table 2 (child) when a new record is made in table 1 (parent) and I can't even install the trigger says there is a problem around 'Then'

here is the code for the two tables and the trigger

CREATE TABLE DIPSTATUS
(
ID INT IDENTITY (1, 1) NOT NULL,
SHIFT VARCHAR(5) NULL,
DIPTIME CHAR(2) NULL,
SCHEDULEDDIPTIME DATETIME NULL,
CONSTRAINT [PK_DIPSTATUS]
PRIMARY KEY CLUSTERED ([ID])
)

**********************************************************

CREATE TABLE DIPSTATUSTIMES
(
ID int IDENTITY (1, 1) NOT NULL,
MID int NOT NULL,
RACK VARCHAR(50) NULL DEFAULT 'CHOOSE RACK',
STATUS VARCHAR(10) NULL DEFAULT 'PENDING',
SHIFT VARCHAR(5) NULL,
SCHEDULEDDIPTIME DATETIME NULL,
ACTUALDIPTIME DATETIME NULL,
CONSTRAINT [PK_DIPSTATUSTIMES]
PRIMARY KEY CLUSTERED ([ID]),
CONSTRAINT FK_DIPSTATUSTIMES
FOREIGN KEY(MID)
REFERENCES DIPSTATUS(ID)
ON DELETE CASCADE
)

**********************************************************

CREATE TRIGGER CR_DIPSTATUSTIMES
ON DIPSTATUS
FOR INSERT
AS
DECLARE @ID INT
DECLARE @SHIFT VARCHAR(5)
DECLARE @DIPTIME DATETIME
DECLARE @COUNT INT
DECLARE @DIPADV DATETIME
SET @ID AS [ID]
SET @SHIFT AS [SHIFT]
SET @DIPTIME AS [DIPTIME]
IF @DIPTIME = 20 AND @SHIFT = 'DAY' THEN
SET @COUNT = 37
SET @DIPADV AS DATEADD (hh , 4, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 20, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 30 AND @SHIFT = 'DAY' THEN
SET @COUNT = 25
SET @DIPADV AS DATEADD (hh , 4, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 30, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 45 AND @SHIFT = 'DAY' THEN
SET @COUNT = 17
SET @DIPADV AS DATEADD (hh , 4, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 45, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 60 AND @SHIFT = 'DAY' THEN
SET @COUNT = 13
SET @DIPADV AS DATEADD (hh , 4, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 60, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 20 AND @SHIFT = 'NIGHT' THEN
SET @COUNT = 37
SET @DIPADV AS DATEADD (hh , 16, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 20, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 30 AND @SHIFT = 'NIGHT' THEN
SET @COUNT = 25
SET @DIPADV AS DATEADD (hh , 16, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 30, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 45 AND @SHIFT = 'NIGHT' THEN
SET @COUNT = 17
SET @DIPADV AS DATEADD (hh , 16, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 45, @DIPADV)
END WHILE
ELSE IF @DIPTIME = 60 AND [SHIFT] = 'NIGHT' THEN
SET @COUNT = 13
SET @DIPADV AS DATEADD (hh , 16, [SCHEDULEDDIPTIME])
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
@COUNT = @COUNT - 1
@DIPADV = DATEADD (n , 60, @DIPADV)
END WHILE
END IF

[Mosser]

I finally relized I was writing the dang trigger like I was coding in VB (stupid me). So I rewrote it and it is loaded. Although it times out before it can finish. I set the time out to 1000 seconds and no avail (that should have been long enough).

here is the new trigger

CREATE TRIGGER CR_DIPSTATUS
ON DIPSTATUS
FOR INSERT
AS
DECLARE @ID INT
DECLARE @SHIFT VARCHAR(5)
DECLARE @DIPTIME CHAR(2)
DECLARE @COUNT INT
DECLARE @DIPADV DATETIME
DECLARE @SCHEDULEDDIPTIME DATETIME
SET @ID = (SELECT ID FROM INSERTED)
SET @SHIFT = (SELECT SHIFT FROM INSERTED)
SET @DIPTIME = (SELECT DIPTIME FROM INSERTED)
SET @SCHEDULEDDIPTIME = (SELECT SCHEDULEDDIPTIME FROM INSERTED)
IF @DIPTIME = 20 AND @SHIFT = 'DAY'
SET @COUNT = 37
SET @DIPADV = DATEADD (hh , 4, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 20, @DIPADV)
IF @DIPTIME = 30 AND @SHIFT = 'DAY'
SET @COUNT = 25
SET @DIPADV = DATEADD (hh , 4, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 30, @DIPADV)
IF @DIPTIME = 45 AND @SHIFT = 'DAY'
SET @COUNT = 17
SET @DIPADV = DATEADD (hh , 4, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 45, @DIPADV)
IF @DIPTIME = 60 AND @SHIFT = 'DAY'
SET @COUNT = 13
SET @DIPADV = DATEADD (hh , 4, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 60, @DIPADV)
IF @DIPTIME = 20 AND @SHIFT = 'NIGHT'
SET @COUNT = 37
SET @DIPADV = DATEADD (hh , 16, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 20, @DIPADV)
IF @DIPTIME = 30 AND @SHIFT = 'NIGHT'
SET @COUNT = 25
SET @DIPADV = DATEADD (hh , 16, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 30, @DIPADV)
IF @DIPTIME = 45 AND @SHIFT = 'NIGHT'
SET @COUNT = 17
SET @DIPADV = DATEADD (hh , 16, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 45, @DIPADV)
IF @DIPTIME = 60 AND @SHIFT = 'NIGHT'
SET @COUNT = 13
SET @DIPADV = DATEADD (hh , 16, @SCHEDULEDDIPTIME)
WHILE @COUNT > 0
INSERT INTO DIPSTATUSTIMES (MID, SHIFT, SCHEDULEDDIPTIME, ACTUALDIPTIME)
VALUES (@ID, @SHIFT, @DIPADV, @DIPADV)
SET @COUNT = @COUNT - 1
SET @DIPADV = DATEADD (n , 60, @DIPADV)

[Mosser]

NV, Got it working.