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?.
