# Database Discussions > Microsoft SQL Server 2008 >  INSERT Trigger for Multi row

## alamsha

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

----------


## rmiao

First of all, don't put this in master db. Better to create your own db and test whatever you like in it.

----------


## alamsha

Yes. I have done that. Does it make a difference?.

----------


## rmiao

It's different issue.

----------


## alamsha

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.

----------

