使用触发器插入另一个表

I want to insert a record after insert with trigger, but it doesn't work properly. it's on an accounting table. First I wrote the trigger and I got this error: The INSERT statement conflicted with the FOREIGN KEY constraint The conflict occurred in database The statement has been terminated. Then I found the key and wrote the trigger on that table and then I got new error: this sqltransaction has completed it is no longer usable sql server and here is the trigger:

我想在插入触发器后插入记录,但它无法正常工作。它在会计表上。首先我写了触发器,我得到了这个错误:INSERT语句与FOREIGN KEY约束发生冲突数据库中发生冲突语句已被终止。然后我找到了密钥并在该表上写了触发器然后我得到了新的错误:这个sqltransaction已经完成它不再可用sql server而且这里是触发器:

USE [Sepidar01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter TRIGGER [INV].[CrmSelectInsertVWInvoiceItems] 
ON  [INV].[InventoryDeliveryItem] 
After Insert
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @CustomerCode varchar(40)
DECLARE @InvoiceRef INT
DECLARE @Price decimal(19, 4)
DECLARE @CreationDate datetime
DECLARE @Quantity decimal(19, 4)
DECLARE @GuID uniqueidentifier
DECLARE @KalaCode int
DECLARE @BaseInvoiceItem int

SELECT @BaseInvoiceItem=(BaseInvoiceItem) FROM INSERTED

select @CreationDate AS [inv.CreationDate],@CustomerCode AS [dl.Code],@Price       
AS [invitem.Price],@Quantity AS [invitem.Quantity], @KalaCode AS [itemst.ItemStockID]
from [Sepidar01].[INV].[InventoryDeliveryItem] as invent
join sls.InvoiceItem as invitem
on BaseInvoiceItem =InvoiceItemID
join sls.Invoice as inv
on InvoiceId = InvoiceRef
join acc.dl as dl
on inv.CustomerRealName like dl.Title
join inv.ItemStock as itemst
on invitem.ItemRef = itemst.ItemRef
where invent.BaseInvoiceItem  = @BaseInvoiceItem

SELECT @GuID AS [bartar_newpaitientId]
  FROM [192.168.0.15].[Bartar_MSCRM].[dbo].[bartar_newpaitientBase] as newPatient
  where newPatient.bartar_CustomerCode = @CustomerCode

INSERT INTO [192.168.0.15].[Test_MSCRM].[dbo].[bartar_callcenterreportBase]
       ([bartar_callcenterreportId]
       ,[OwnerId]
       ,[statecode]
       ,[CreatedOn]
       ,[bartar_Date] 
       ,[bartar_patientName]
       ,[bartar_Brand]
       ,[bartar_Paste]
       ,[bartar_Bag]
       ,[bartar_ACC]
       ,[bartar_NextDateSales]
       ,[bartar_name])
 VALUES
       (NEWID(),'315BE87D-0035-E511-80B5-0007E9498006',0,@CreationDate,@CreationDate,@GuID,0,0,0,0,@CreationDate,'System')

END

So how can I solve this problem?

那么我该如何解决这个问题呢?

1 个解决方案

#1

ِYou must change your first select like this:

你必须改变你的第一个选择:

select @CreationDate=[inv.CreationDate],@CustomerCode=[dl.Code],
@Price=[invitem.Price],
@Quantity=[invitem.Quantity], @KalaCode=[itemst.ItemStockID]
from [Sepidar01].[INV].[InventoryDeliveryItem] as invent
join sls.InvoiceItem as invitem
on BaseInvoiceItem =InvoiceItemID
join sls.Invoice as inv
on InvoiceId = InvoiceRef
join acc.dl as dl
on inv.CustomerRealName like dl.Title
join inv.ItemStock as itemst
on invitem.ItemRef = itemst.ItemRef
where invent.BaseInvoiceItem  = @BaseInvoiceItem

And the second one:

第二个:

SELECT @GuID=[bartar_newpaitientId]
  FROM [192.168.0.15].[Bartar_MSCRM].[dbo].[bartar_newpaitientBase]
    as newPatient
  where newPatient.bartar_CustomerCode = @CustomerCode

So your vars get value.

所以你的vars值得。

And if you get this error Msdtc on server is unavailable, this is the solution: fix error

如果你得到这个错误服务器上的Msdtc不可用,这是解决方案:修复错误

1 个解决方案

#1

更多相关文章

随机推荐