-
T-SQL Trigger
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
Thanks,
MO
Similar Threads
-
By Subbu in forum Database
Replies: 0
Last Post: 03-20-2002, 12:37 PM
-
By santosh in forum Database
Replies: 1
Last Post: 10-05-2001, 09:16 AM
-
By David Jones in forum Database
Replies: 0
Last Post: 08-31-2001, 12:22 PM
-
By Chandra in forum VB Classic
Replies: 0
Last Post: 06-22-2000, 07:30 AM
-
By john Harkin in forum Database
Replies: 0
Last Post: 05-04-2000, 11:29 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|