sqlserver 和 oracle 触发器还是有区别的,sqlserver 没有for each row 的概念,执行一条语句,无论多少条数据,触发一次,所以要用游标循环,做逻辑判断。当然,也可以考虑同临时表,再遍历临时表,以下是对两种方法的实现
- 游标
ALTER TRIGGER 触发器名字
ON 表
AFTER INSERT, UPDATE,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE Cursor_Inserted CURSOR LOCAL FORWARD_ONLY FOR
SELECT [ID]
FROM Inserted;
DECLARE @ID INT;
OPEN Cursor_Inserted;
FETCH NEXT FROM Cursor_Inserted INTO @ID;
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
--subject
....
--read next
FETCH NEXT FROM Cursor_Inserted INTO @ID;
END
CLOSE Cursor_Inserted;
DEALLOCATE Cursor_Inserted;
END
也可以通过一下语句判断增删改的操作
if exists(select 1 from inserted) and not exists( select 1 from deleted) --insert 操作
else if exists(select 1 from inserted) and exists(select 1 from deleted) --update 操作
else if not exists(select 1 from inserted) and exists(select 1 from deleted) --delete 操作
2. 临时表
Create table #temp(ID int IDENTITY(1,1) PRIMARY KEY, PersonID int,Name varchar(50))
Insert into #temp(PersonID, Name) Select ID, Name From inserted
Declare @i Int=1,@is Int=0
SELECT @is =MAX([ID]) FROM #temp
WHILE @i<@is
BEGIN
...
SET @i=@i+1;
END