dcsimg
Results 1 to 5 of 5

Thread: INSERT Trigger for Multi row

  1. #1
    Join Date
    Jan 2009
    Posts
    3

    INSERT Trigger for Multi row

    This is the final code, i have / MS SQL 2008:

    USE [master]
    GO

    /****** Object: Table [dbo].[TagHistory] Script Date: 01/27/2009 21:36:21 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[TagHistory](
    [TagID] [int] NULL,
    [StationName] [varchar](17) NULL,
    [TagName] [varchar](33) NULL,
    [TagValue] [float] NULL,
    [TagString] [varchar](257) NULL,
    [TagTimeStamp] [datetime] NULL,
    [MiliSec] [smallint] NULL,
    [Flag] [tinyint] NULL
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


    ALTER TABLE [dbo].[TagHistory] WITH CHECK ADD CONSTRAINT [check_Flag]
    CHECK (([Flag]=(2)))
    GO

    USE [master]
    GO
    /****** Object: Trigger [dbo].[trgTagHistory_Insert] Script Date: 01/26/2009 23:21:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[trgTagHistory_Insert]
    ON [dbo].[TagHistory]
    INSTEAD OF INSERT
    AS
    DECLARE @TagName1 varchar(33), @TagValue1 float

    SELECT TOP 3 *
    FROM TagHistory
    ORDER BY TagTimeStamp DESC, MiliSec Desc

    SELECT *
    FROM INSERTED
    ORDER BY TagTimeStamp DESC, MiliSec Desc

    IF EXISTS (
    SELECT *
    FROM inserted AS i
    OUTER APPLY (
    --SELECT TOP 1 WITH TIES
    SELECT TOP 1 WITH TIES
    yt.TagValue
    FROM dbo.TagHistory AS yt
    WHERE yt.TagName = i.TagName
    ORDER BY yt.TagTimeStamp DESC, yt.Milisec DESC
    ) AS s
    WHERE i.TagValue = (s.TagValue)
    )
    BEGIN
    RAISERROR('Two or more consecutive records over TagName and TagTimeStamp have the same TagValue.', 16, 1)
    ROLLBACK TRAN
    END
    ELSE
    INSERT INTO dbo.TagHistory
    SELECT * FROM INSERTED
    -----------------------------
    Test query:

    INSERT INTO dbo.TagHistory(TagID, TagName, TagValue, TagTimeStamp,
    MiliSec, Flag)
    VALUES (4, 'DIGI4', 1, '20090201 15:10:04.000',569, 3)

    INSERT INTO dbo.TagHistory(TagID, TagName, TagValue, TagTimeStamp,
    MiliSec, Flag)
    VALUES (5, 'DIGI5', 1, '20090201 15:10:05.000',569, 2)

    INSERT INTO dbo.TagHistory(TagID, TagName, TagValue, TagTimeStamp,
    MiliSec, Flag)
    VALUES (6, 'DIGI6', 1, '20090201 15:10:06.000',569, 2)

    Error Message:

    (0 row(s) affected)

    (1 row(s) affected)
    Msg 547, Level 16, State 0, Procedure trgTagHistory_Insert, Line 33
    The INSERT statement conflicted with the CHECK constraint "check_Flag". The conflict occurred in database "master", table "dbo.TagHistory", column 'Flag'.
    The statement has been terminated.

    Problem to be solved:

    The INSTEAD OF INSERT trigger exits when it comes across any invalid record. I want it to continue with all other valid records. Can someone help me?.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    First of all, don't put this in master db. Better to create your own db and test whatever you like in it.

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    Yes. I have done that. Does it make a difference?.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    It's different issue.

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    Agreed, let us keep it aside. Can any one provide a solution for the problem i have posted which is very urgent at the moment?. Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •