EF Core Error : statement contains an OUTPUT clause
今天使用Azure Data Sync進行Azure Sql中幾個資料表的同步，由於Azure Data Sync是透過trigger進行資料追蹤，所以會自動在所需同步的資料表上建立三個Trigger(Insert、Update、Delete)，由於我們專案資料庫存取是採取EF Core，同事發現Savechange後會拋出如下錯誤
..cannot have any enabled triggers if the statement contains an OUTPUT clause
或者
Could not save changes because the target table has database triggers. Please configure your table...查看EF Core Breaking changes才知道，原來EF Core 7開始該行為有所改變，主要是提高Savechanges效率，如想要保留trigger且EF Core執行正常，就得回到(EF Core 6)舊行為，修改相關資料表的Configuration如下
dotnet ef dbcontext scaffold Name=ConnectionStrings:DefaultConnection Microsoft.EntityFrameworkCore.SqlServer --data-annotations --context-dir Data --force測試Trigger
CREATE TRIGGER trgAfterInsert on [dbo].[DistributionChannel]
FOR INSERT
AS declare @id int, @dsaDistributionChannel varchar(6), @distributionChannelDesc varchar(50), @sunT5ChanneType varchar(6), @createdDate datetime2, @audit_action varchar(100);
select @id=i.Id from inserted i;
select @dsaDistributionChannel=i.DsaDistributionChannel from inserted i;
select @distributionChannelDesc=i.DistributionChannelDesc from inserted i;
select @sunT5ChanneType=i.SunT5ChanneType from inserted i;
select @createdDate=i.CreatedDate from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into DistributionChannel_Audit([Id],[DsaDistributionChannel],[DistributionChannelDesc],[SunT5ChanneType],[CreatedDate],AuditAction)
values (@id,@dsaDistributionChannel,@distributionChannelDesc,@sunT5ChanneType,@createdDate,@audit_action);
PRINT 'AFTER INSERT trigger fired.'
reference:
