环境
- 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
问题解决:
- 出现错误
Database operation expected to affect 1 row(s) but actually affected 0 row(s)
insert最后没有返回受影响的行数
– 返回受影响的行数
select * from @t
- 提示Cursor已存在
为cursor添加LOCAL描述符
DECLARE insert_cursor CURSORLOCAL
FOR
关于Deleted和Inserted表
表 | Insert | Delete | Update |
---|---|---|---|
Inserted | 插入列 | 空 | 修改前的列 |
Deleted | 空 | 删除列 | 修改后的列 |
所以我们可以根据Deleted表来判断是Insert还是update.