27,580
社区成员
发帖
与我相关
我的任务
分享
exec sp_addlinkedserver 'U8SV','','SQLOLEDB','远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'U8SV','false',null,'用户名','密码'go
创建了链接服务器,查询分析器里查询远程服务器上表数据没有问题,但是执行触发器的时候就提示insert into U8SV.CRMKLRJ.dbo.费用申请单主表(WorkFlowGUID,WorkFlowInstanceCode,AgencyID,DepartmentID,Initiateuserguid,InitiateDate,IsFinished,
申请人,创建人,备注,金额合计大写,金额合计小写,付款方式,收款银行,银行账号,申请金额合计,申请金额合计大写)
select @gid,@WorkFlowInstanceCode,@AgencyID,@DepartmentID,@Initiateuserguid,CONVERT(varchar(100), GETDATE(), 21),0,@申请人,@申请人,
(select cMemo from PO_Pomain where POID=(select top 1 POID from inserted)),
'零元整',0,(select cDefine1 from PO_Pomain where POID=(select top 1 POID from inserted)),(select cVenBank from PO_Pomain
where POID=(select top 1 POID from inserted)),
(select cVenAccount from PO_Pomain where POID=(select top 1 POID from inserted)),(select SUM(iNatSum) from inserted),
dbo.[fn_getformatmoney]((select SUM(iNatSum) from inserted))
select * from U8SV.CRMKLRJ.dbo.费用申请单主表
就没报错,但是换成插入语句就报错,这是为什么if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[Makebaoxiaodan]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger Makebaoxiaodan
go
create trigger Makebaoxiaodan
on PO_Podetails
after insert,update
as
begin tran tran_Update --开始事务
declare @tran_error int;
set @tran_error=0;
begin try
--插主表数据
declare @gid uniqueidentifier,
@type nvarchar(80), --业务类型
@WorkFlowInstanceCode nvarchar(100),
@AgencyID int,
@DepartmentID int,
@Initiateuserguid uniqueidentifier,
@申请人 nvarchar
set @gid=newid()
select @type=cBusType from PO_Pomain where POID=(select top 1 POID from UFDATA_123_2018.dbo.inserted)
select @WorkFlowInstanceCode='费用申请单-'+CONVERT(varchar(100), GETDATE(), 112)+'-'+cast(right(MAX(WorkFlowInstanceCode),4)+1 as nvarchar) from U8SV.CRMKLRJ.dbo.费用申请单主表
select @AgencyID=AgencyID,@DepartmentID=DepartmentID from U8SV.CRMKLRJ.dbo.DepartmentTable where DepartmentName=(select cDepName from Department where cDepCode=(select cDepCode from PO_Pomain where POID=(select top 1 POID from inserted)))
select @Initiateuserguid=UserGuid from U8SV.CRMKLRJ.dbo.EmployeeTable where Username=(select cMaker from PO_Pomain where
POID=(select top 1 POID from inserted))
select @申请人=cMaker from PO_Pomain where POID=(select top 1 POID from inserted)
insert into U8SV.CRMKLRJ.dbo.费用申请单主表(WorkFlowGUID,WorkFlowInstanceCode,AgencyID,DepartmentID,Initiateuserguid,InitiateDate,IsFinished,
申请人,创建人,备注,金额合计大写,金额合计小写,付款方式,收款银行,银行账号,申请金额合计,申请金额合计大写)
select @gid,@WorkFlowInstanceCode,@AgencyID,@DepartmentID,@Initiateuserguid,CONVERT(varchar(100), GETDATE(), 21),0,@申请人,@申请人,
(select cMemo from dbo.PO_Pomain where POID=(select top 1 POID from dbo.inserted)),
'零元整',0,(select cDefine1 from dbo.PO_Pomain where POID=(select top 1 POID from dbo.inserted)),(select cVenBank from dbo.PO_Pomain
where POID=(select top 1 POID from dbo.inserted)),
(select cVenAccount from dbo.PO_Pomain where POID=(select top 1 POID from dbo.inserted)),(select SUM(iNatSum) from dbo.inserted),
dbo.[fn_getformatmoney]((select SUM(iNatSum) from dbo.inserted))
end try
begin catch
set @tran_error=@tran_error+1; --加分号或不加都能正常执行
end catch
if(@tran_error>0)
begin
rollback tran; --执行出错,回滚事务(不指定事务名称)
end
else
begin
commit tran; --没有异常,提交事务(不指定事务名称)
if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[Makebaoxiaodan]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger Makebaoxiaodan
go
create trigger Makebaoxiaodan
on PO_Podetails
after insert,update
as
begin tran tran_Update --开始事务
declare @tran_error int;
set @tran_error=0;
begin try
--插主表数据
declare @gid uniqueidentifier,
@type nvarchar(80), --业务类型
@WorkFlowInstanceCode nvarchar(100),
@AgencyID int,
@DepartmentID int,
@Initiateuserguid uniqueidentifier,
@申请人 nvarchar
set @gid=newid()
select @type=cBusType from PO_Pomain where POID=(select top 1 POID from UFDATA_123_2018.dbo.inserted)
select @WorkFlowInstanceCode='费用申请单-'+CONVERT(varchar(100), GETDATE(), 112)+'-'+cast(right(MAX(WorkFlowInstanceCode),4)+1 as nvarchar) from U8SV.CRMKLRJ.dbo.费用申请单主表
select @AgencyID=AgencyID,@DepartmentID=DepartmentID from U8SV.CRMKLRJ.dbo.DepartmentTable where DepartmentName=(select cDepName from Department where cDepCode=(select cDepCode from PO_Pomain where POID=(select top 1 POID from inserted)))
select @Initiateuserguid=UserGuid from U8SV.CRMKLRJ.dbo.EmployeeTable where Username=(select cMaker from PO_Pomain where
POID=(select top 1 POID from inserted))
select @申请人=cMaker from PO_Pomain where POID=(select top 1 POID from inserted)
end try
begin catch
set @tran_error=@tran_error+1; --加分号或不加都能正常执行
end catch
if(@tran_error>0)
begin
rollback tran; --执行出错,回滚事务(不指定事务名称)
end
else
begin
commit tran; --没有异常,提交事务(不指定事务名称)