创建触发器时提示错误信息:不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ArvinStudy/article/details/8238539

在创建触发器时提示错误信息:

不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列

解决方法

Create TRIGGER tr_DataSyncInsert 
   ON  dbo.Open_AllItem
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	INSERT INTO dbo.Open_Item
	SELECT 
	*
	FROM dbo.Open_AllItem Where [Item_ID] IN (Select [Item_ID] From inserted)
END
GO

通过 Where [Item_ID] IN (Select [Item_ID] From inserted)来解决。

展开阅读全文

触发器inserteddeleted表请教!

03-25

我有一个触发器:rnCREATE TRIGGER TR_SD_ProdNote_M_IUrnON dbo.SD_ProdNote_MrnFOR Insert, Update rnASrnBEGINrn DECLARE @GUID uniqueidentifierrn IF Exists( rn[color=#FF0000] SELECT i.GUID rn FROM inserted i left join deleted d on i.GUID = d.GUIDrn WHERE IsNull(i.sIsPurchase,'N') = 'Y' and IsNull(d.sIsPurchase,'N') <> 'Y' )[/color]rn BEGINrn -- 对表PP_DyeNotice_D的插入操作还需要在SD_ProdNote_D的触发器中编写. 因为PP_DyeNotice_M的数据先于PP_DyeNotice_D插入.rn IF Exists(SELECT b.GUID rn FROM inserted i inner join rn PP_DyeNotice_M b on i.GUID = b.SD_ProdNote_MGUID left joinrn deleted d on i.GUID = d.GUID rn WHERE sNoticeType = 'W'rn and IsNull(i.sIsPurchase,'N') = 'Y' and IsNull(d.sIsPurchase,'N') <> 'Y')rn BEGINrnrn UPDATE PP_DyeNotice_M rn SET fOrderQuan = od.fStandQuanrn FROM inserted i inner join rn SD_Order_D od on i.SD_Order_DGUID = od.GUID left joinrn deleted d on i.GUID = d.GUID rn WHERE i.GUID = PP_DyeNotice_M.SD_ProdNote_MGUID and PP_DyeNotice_M.sNoticeType = 'W'rn and IsNull(i.sIsPurchase,'N') = 'Y' and IsNull(d.sIsPurchase,'N') <> 'Y'rn ENDrn ELSErn BEGIN rn SET @GUID = newid()rn INSERT INTO PP_DyeNotice_Mrn (GUID, SD_ProdNote_MGUID, sNoticeNo, dProdDeliDate, sNoticeType, rn sProdSpec, fOrderQuan, fShrinkRate, sStatusID, dInputDate )rn SELECT @GUID, i.GUID, 'CDN'+i.sProNoticeCode, i.dProdDeliDate, 'W', rn sSpecification, od.fStandQuan, IsNull(fWasteRate,0), 'N', getdate()rn FROM inserted i inner join rn SD_Order_D od on i.SD_Order_DGUID = od.GUID inner join rn BD_Part bp on od.BD_PartGUID = bp.GUID left join rn deleted d on i.GUID = d.GUIDrn WHERE IsNull(i.sIsPurchase,'N') = 'Y' and IsNull(d.sIsPurchase,'N') <> 'Y'rn -- 插入PP_DyeNotice_Drnrn INSERT INTO PP_DyeNotice_D rn (GUID, PP_DyeNotice_MGUID, SD_ProdNote_DGUID, sItemNO, sCheckColor, fQuantity, sSaleOrderCode, sSaleOrderItem, rn sCustomerPO, sStyle, iSampleQuantity, dProdDeliDate, sStatusID, dInputDate, sRemark)rn SELECT newid(), @GUID, pd.GUID, pd.sItemNO, pd.sCheckColor, pd.fStandQuan, om.sSaleOrderNO, od.sItemNO,rn pd.sCustomerPO, pd.sStyle, pd.fSampleQuantity, i.dProdDeliDate, 'N', getdate(), 'AutoInsert(外购成品布)'rn FROM SD_ProdNote_D pd inner joinrn inserted i on pd.SD_ProdNote_MGUID = i.GUID inner join rn sd_order_d od on i.SD_Order_DGUID = od.guid inner joinrn sd_order_m om on od.SD_Order_MGUID = om.guid left joinrn deleted d on i.GUID = d.GUIDrn WHERE IsNull(i.sIsPurchase,'N') = 'Y' and IsNull(d.sIsPurchase,'N') <> 'Y'rn ENDrnrn ENDrnrn我第一次保存的时候触发器就会有问题:只往PP_DyeNotice_M表写了数据,PP_DyeNotice_D的数据就是写不进去,非要再重新触发这个触发器,PP_DyeNotice_D才会写进数据。rn是不是我红色部分出了问题?请大侠指教!谢谢!rn 论坛

没有更多推荐了,返回首页