<SQL> SQL instead of trigger 触发器

环境

• Azure SQL Database
• Visual Studio 2019
• ASP NET CORE 2.2

写Trigger, 使用cursor遍历

CREATE TRIGGER [TriggerBeforeInsertUpdate]
ON [dbo].[ChineseCharacter]
INSTEAD OF INSERT, Update
AS
BEGIN
	SET NOCOUNT ON
	declare @t table([id] bigint not null);
	declare @insertedline int = 0;
	declare @id bigint;
	declare @oldid bigint;
	declare @data nvarchar(max);
	-- declare a cursor
	-- 定义为local避免出现已经定义
	DECLARE insert_cursor CURSOR LOCAL FOR 
	SELECT	ID, Data
		from inserted;
 
	-- open cursor and fetch first row into variables
	OPEN insert_cursor
	FETCH NEXT FROM insert_cursor 
			into @id, @data,
	
	-- 开始循环遍历
	-- check for a new row
	WHILE @@FETCH_STATUS=0
	BEGIN
		-- do complex operation here 
		select @oldid = ID from deleted where ID = @id;
	
		if @oldid is null 
		begin
			-- 说明执行的是插入
			set @insertedline  += 1;
			insert into @t values (@id);
		end
		else 
		begin
			-- 说明执行的是更新 
		end

		-- get next available row into variables
		FETCH NEXT FROM insert_cursor 
			into @id, @data,

	END

	close insert_cursor
	Deallocate insert_cursor
	
	-- 返回受影响的行数
   if @insertedline > 0
   	  select * from @t;
END

问题解决：

1. 出现错误Database operation expected to affect 1 row(s) but actually affected 0 row(s)

insert最后没有返回受影响的行数
– 返回受影响的行数
select * from @t

2. 提示Cursor已存在

为cursor添加LOCAL描述符
DECLARE insert_cursor CURSOR LOCAL FOR

关于Deleted和Inserted表

表	Insert	Delete	Update
Inserted	插入列	空	修改前的列
Deleted	空	删除列	修改后的列

所以我们可以根据Deleted表来判断是Insert还是update.