當該表上有“而不是插入”觸發器時，只有最後一行插入到表中

我有這個觸發器

CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test]
instead of insert
AS
begin
declare @name nvarchar (50),@city nvarchar (11),
@tdate datetime , @fid int,@fopd datetime;
select @name =i.name from inserted i;
select @city =i.city from inserted i;
select @tdate =i.date from inserted i;
select @fid =i.fid from inserted i;
select @fopd = Faculty.fopd from Faculty 
   where fid= @fid;
if (@fopd > @tdate )
begin
   RAISERROR('Cannot insert ',16,1);
   ROLLBACK TRANSACTION
   RETURN;
   -- stop on error
end
insert into test
values (@name,@city,@tdate,@fid); 
end

和插入：

insert into test
values('a','h','2014-10-13',1),
('s','h','2014-10-14',2),
('y','d','2014-10-15',2),
('s','h','2014-10-18',1),
('m','a','2014-10-20',1),
('a','h','2014-10-28',2)

然後我select * from test.. 只插入最後一個值('a','h','2014-10-28',2)

我必須一次插入一個！

我使用for了觸發器..所有行都插入了但在另一個測試中它接受了錯誤的值：

insert into test
values('a','h','2014-10-13',1),
('s','h','2014-10-14',2),
('y','d','2014-10-15',2),
('s','h','1990-10-18',1), -- this is wrong must not be inserted or stop all 
('m','a','2014-10-20',1),
('a','h','2014-10-28',2)

什麼地方出了錯 ！？

問題是您的比較針對的是單個記錄，但 INSERTED 偽表包含多行（所有行都插入到事務中）。您可以使用以下方法解決此問題：

CREATE TRIGGER inTrigger ON Data
INSTEAD OF INSERT
AS
BEGIN

 IF EXISTS(
   SELECT * 
   FROM INSERTED i 
   INNER JOIN Faculty f ON f.Id = i.Fid
   WHERE f.fopd > i.Date
 )
 BEGIN
   RAISERROR('Cannot insert ',16,1);
 END

 INSERT INTO test (id, fid, date)
 SELECT i.* 
 FROM INSERTED i
 INNER JOIN Faculty f ON f.Id = i.Fid
 WHERE f.fopd <= i.Date

END

此觸發器標識 INSERTED 表中與失敗條件匹配的任何記錄，如果遇到它會引發錯誤。然後它繼續插入不違反條件的記錄。如果願意，您可以輕鬆地修改它以回滾整個事務。

引用自：https://dba.stackexchange.com/questions/267338