DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: T-SQL Trigger

  1. #1
    Join Date
    Jun 2007
    Location
    Oregon
    Posts
    14

    Question 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

  2. #2
    Join Date
    Jun 2007
    Location
    Oregon
    Posts
    14

    Trigger loads now but times out

    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)
    Thanks,

    MO

  3. #3
    Join Date
    Jun 2007
    Location
    Oregon
    Posts
    14
    NV, Got it working.
    Thanks,

    MO

Similar Threads

  1. COM Component in SQL Trigger
    By Subbu in forum Database
    Replies: 0
    Last Post: 03-20-2002, 12:37 PM
  2. BEFORE UPDATE TRIGGER SQL SERVER 2000
    By santosh in forum Database
    Replies: 1
    Last Post: 10-05-2001, 09:16 AM
  3. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  4. Replies: 0
    Last Post: 06-22-2000, 07:30 AM
  5. 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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center