SQL 触发器调用存储过程（建表）后再执行动态语句报错

腾信软创 2016-01-29 11:47:43
问题如标题：SQL 触发器调用存储过程（建表）后再执行动态语句报错
报错信息：
消息 3970，级别 16，状态 2，第 1 行
此操作与此事务上的另一个挂起操作冲突。此操作失败。

经过查看出错地方为节点1和节点2之间。

drop procedure createTable;

go



create procedure createTable

(

 @mainTB nvarchar(100),

 @listTB nvarchar(100)

)

as

	-- 建表

	exec ('CREATE TABLE a') at zzx_link;



	exec ('CREATE TABLE b') at zzx_link;

go



drop TRIGGER syncForUpdate;

go





CREATE TRIGGER syncForUpdate

on table

after update,insert

as

begin

	declare @mainTableName nvarchar(100);

	declare @listTableName nvarchar(100);

	declare @mainTb nvarchar(100);

	declare @listTb nvarchar(100);

	declare @sql1 nvarchar(2550);

	declare @sql2 nvarchar(2550);

	declare @field0002 nvarchar(100);

	declare @field0003 nvarchar(100);

	set @mainTb = 'priceMainTable_a';

	set @listTb = 'priceListTable_b';

	set @mainTableName = 'zzx_link.database.dbo.'+'priceMainTable_a';

	set @listTableName = 'zzx_link.database.dbo.'+'priceListTable_b';

	-- 存在即更新，否则新增（表头+表体）

	if EXISTS (SELECT 1)

		select 2

		-- ......

	else

		BEGIN

			-- 建表，节点1

			exec createTable @mainTB,@listTB;

			-- 表头：新增，节点2

			set @sql1 = N'insert into '+@mainTableName+' (id,field0001,field0002,field0003)

						select isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10000),20160125+@field0002,@field0002,@field0003

						 where not exists (select 1

											 from '+@mainTableName+');';

			exec sp_executesql @sql1,

						N'@field0002 nvarchar(100),@field0003 nvarchar(100)',

						@field0002,@field0003;

			

			-- 表体：新增，节点3

			set @sql2 = N'insert into '+@listTableName+' (ID,field0009,field0004,field0005,field0006,field0007,field0008)

						values (isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10001),@field0009,@field0004,@field0005,@field0006,@field0007,@field0008);';

			exec sp_executesql @sql2,

						N'@field0009 nvarchar(100),@field0004 nvarchar(100),@field0005 nvarchar(100),@field0006 nvarchar(100),@field0007 nvarchar(100),@field0008 nvarchar(100)',

						@field0009,@field0004,@field0005,@field0006,@field0007,@field0008; 

		END

end
...全文
421 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
腾信软创 2016-01-30
  • 打赏
  • 举报
 回复
引用 8 楼 roy_88 的回复:
-- 存在即更新，否则新增（表头+表体） 这里判断改改 
 -- 存在即更新，否则新增（表头+表体）
    if EXISTS (SELECT  * 
                 FROM dbo.SysObjects 
                WHERE name = @mainTb
                  AND OBJECTPROPERTY(ID, 'IsTable') = 1)
在createPriceTable里判断表存在时不创建 用AT 方法output实现 按以上把语句改正确再调试
触发器剔除所有代码，只留下调用存储过程的exec createPriceTable @mainTB,@listTB,'','',@mainTableName,@listTableName; 然后把生成数据的动态语句放到建表的存储过程中，还是报错： 消息 50000，级别 16，状态 1，过程 createPriceTable，第 64 行 操作1！ 消息 3970，级别 16，状态 2，第 1 行 此操作与此事务上的另一个挂起操作冲突。此操作失败。 下面为SQL 

drop procedure createPriceTable;
go

create procedure createPriceTable
(
 @mainTB nvarchar(100),
 @listTB nvarchar(100),
 @sql1 nvarchar(500),
 @sql2 nvarchar(500),
 @mainTableName nvarchar(100),
 @listTableName nvarchar(100)
)
as
	BEGIN 
	begin
	 begin TRANSACTION 
		-- 建表
		exec ('USE [OA]
			  CREATE TABLE '+@mainTB+'(
					ID numeric(19, 0) NOT NULL,
					state int NULL,
					start_member_id varchar(25) NULL,
					start_date datetime NULL,
					approve_member_id varchar(25) NULL,
					approve_date datetime NULL,
					finishedflag int NULL,
					ratifyflag int NULL,
					ratify_member_id varchar(25) NULL,
					ratify_date datetime NULL,
					sort int NULL,
					modify_member_id varchar(25) NULL,
					modify_date datetime NULL,
					field0001 nvarchar(255) NULL,
					field0002 nvarchar(255) NULL,
					field0003 nvarchar(255) NULL,
				PRIMARY KEY CLUSTERED 
				(
					ID ASC
				)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
				ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
				) ON [PRIMARY];') at zzx_link;
		
		exec ('USE [OA]
			  CREATE TABLE '+@listTB+'(
					ID numeric(19, 0) NOT NULL,
					formmain_id numeric(19, 0) NULL,
					sort int NULL,
					field0004 nvarchar(255) NULL,
					field0005 nvarchar(255) NULL,
					field0006 nvarchar(255) NULL,
					field0007 nvarchar(255) NULL,
					field0008 nvarchar(255) NULL,
					field0009 nvarchar(255) NULL,
				PRIMARY KEY CLUSTERED 
				(
					ID ASC
				)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
				ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
				) ON [PRIMARY];') at zzx_link;
		begin
		commit TRANSACTION;
		end
		end 
		begin
		RAISERROR ('操作1！' , 16, 1) WITH NOWAIT
		set @sql1 = N'insert into '+@mainTableName+' (id,field0001,field0002,field0003)
					select isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10000),20160125,@cCusCode,@cCusName
					 where not exists (select 1
										 from '+@mainTableName+');';
		exec sp_executesql @sql1,
					N'@cCusCode nvarchar(100),@cCusName nvarchar(100)',
					'1','2';
		RAISERROR ('操作2！' , 16, 1) WITH NOWAIT
		-- 表体：新增
		set @sql2 = N'insert into '+@listTableName+' (ID,field0009,field0004,field0005,field0006,field0007,field0008,formmain_id)
					values (isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10001),@AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost,11);';
		exec sp_executesql @sql2,
					N'@AutoID nvarchar(100),@cInvCode nvarchar(100),@cInvName nvarchar(100),@cInvStd nvarchar(100),@cComUnitCode nvarchar(100),@iInvSaleCost nvarchar(100)',
					'1','2','3','4','5','6';
					end
	end
go

drop TRIGGER JY_syncSA_CusUPrice_For_Update;
go

--价格表：
--1.手动修改OA表的排序规则Chinese_PRC_90为Chinese_PRC
--2.替换OA-U8的链接数据库
-- 问题1.无法判断表是否存在；2.exec sp_executesql同时执行报错。
CREATE TRIGGER JY_syncSA_CusUPrice_For_Update
on SA_CusUPrice
after update,insert
as
begin
	declare @mainTableName nvarchar(100);
	declare @listTableName nvarchar(100);
	declare @mainTb nvarchar(100);
	declare @listTb nvarchar(100);
	set @mainTb = 'priceMainTable_'+@cCusCode;
	set @listTb = 'priceListTable_'+@cCusCode;
	set @mainTableName = 'zzx_link.OA.dbo.'+'priceMainTable_'+@cCusCode;
	set @listTableName = 'zzx_link.OA.dbo.'+'priceListTable_'+@cCusCode;
		BEGIN
			-- 建表，跨服务器问题
			exec createPriceTable @mainTB,@listTB,'','',@mainTableName,@listTableName;
		END
end
腾信软创 2016-01-30
  • 打赏
  • 举报
 回复
问题已解决，如有需要可进个人空间查看
中国风 2016-01-29
  • 打赏
  • 举报
 回复
看你触发器的结构根本没必要去动态去实现
中国风 2016-01-29
  • 打赏
  • 举报
 回复
在触发器结尾加上试试 IF @@TRANCOUNT>0 COMMIT TRAN 或 放在触 发器里 
 -- 建表
    exec ('CREATE TABLE a') at zzx_link;
 
    exec ('CREATE TABLE b') at zzx_link;
中国风 2016-01-29
  • 打赏
  • 举报
 回复
zzx_link.database.dbo

链接服务器？MSDTC配置，应该是启用了分布式事务
中国风 2016-01-29
  • 打赏
  • 举报
 回复
-- 存在即更新，否则新增（表头+表体） 这里判断改改 
 -- 存在即更新，否则新增（表头+表体）
    if EXISTS (SELECT  * 
                 FROM dbo.SysObjects 
                WHERE name = @mainTb
                  AND OBJECTPROPERTY(ID, 'IsTable') = 1)
在createPriceTable里判断表存在时不创建 用AT 方法output实现 按以上把语句改正确再调试
腾信软创 2016-01-29
  • 打赏
  • 举报
 回复
引用 5 楼 roy_88 的回复:
看提示信息，这类信息只会在并发时才会出现 你在触发器里用链接服务器，看你的方法，只要MSDTC配置正确，防火墙设置好或关闭，就会正常
上面的代码弄乱了，下面的是，版主帮助看看吧，实在搞不定了...... 
---- 启用RPC
--exec sp_serveroption @server='zzx_link' , @optname= 'rpc', @optvalue ='TRUE'  
--exec sp_serveroption @server='zzx_link' , @optname= 'rpc out', @optvalue='TRUE'  


drop procedure createPriceTable;
go

-- 创建价格表：priceTable_01001(经销商编码)
create procedure createPriceTable
(
 @mainTB nvarchar(100),
 @listTB nvarchar(100)
)
as
SET NOCOUNT ON
	BEGIN 
	 begin transaction 
		-- 建表
		exec ('USE [OA]
			  CREATE TABLE '+@mainTB+'(
					ID numeric(19, 0) NOT NULL,
					state int NULL,
					start_member_id varchar(25) NULL,
					start_date datetime NULL,
					approve_member_id varchar(25) NULL,
					approve_date datetime NULL,
					finishedflag int NULL,
					ratifyflag int NULL,
					ratify_member_id varchar(25) NULL,
					ratify_date datetime NULL,
					sort int NULL,
					modify_member_id varchar(25) NULL,
					modify_date datetime NULL,
					field0001 nvarchar(255) NULL,
					field0002 nvarchar(255) NULL,
					field0003 nvarchar(255) NULL,
				PRIMARY KEY CLUSTERED 
				(
					ID ASC
				)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
				ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
				) ON [PRIMARY];') at zzx_link;

		exec ('USE [OA]
			  CREATE TABLE '+@listTB+'(
					ID numeric(19, 0) NOT NULL,
					formmain_id numeric(19, 0) NULL,
					sort int NULL,
					field0004 nvarchar(255) NULL,
					field0005 nvarchar(255) NULL,
					field0006 nvarchar(255) NULL,
					field0007 nvarchar(255) NULL,
					field0008 nvarchar(255) NULL,
					field0009 nvarchar(255) NULL,
				PRIMARY KEY CLUSTERED 
				(
					ID ASC
				)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
				ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
				) ON [PRIMARY];') at zzx_link;
	IF @@TRANCOUNT>0
		COMMIT TRAN
	end
SET NOCOUNT OFF
go

drop TRIGGER JY_syncSA_CusUPrice_For_Update;
go


CREATE TRIGGER JY_syncSA_CusUPrice_For_Update
on SA_CusUPrice
after update,insert
as
begin
	declare @mainTableName nvarchar(100);
	declare @listTableName nvarchar(100);
	declare @mainTb nvarchar(100);
	declare @listTb nvarchar(100);
	declare @rowsCount nvarchar(100);
	declare @sql nvarchar(2550);
	declare @sqls nvarchar(1000);
	declare @sql1 nvarchar(2550);
	declare @sql2 nvarchar(2550);
	declare @AutoID nvarchar(100);
	declare @cCusCode nvarchar(100);
	declare @cCusName nvarchar(100);
	declare @cInvCode nvarchar(100);
	declare @cInvName nvarchar(100);
	declare @cInvStd nvarchar(100);
	declare @cComUnitCode nvarchar(100);
	declare @iInvSaleCost nvarchar(100);
	set @cCusCode = (select cCusCode from inserted ins);
	set @cCusName = (select cus.cCusName from inserted ins,Customer cus where ins.cCusCCode = cus.cCusCode);
	set @AutoID = (select ins.AutoID from inserted ins);
	set @cInvCode = (select ins.cInvCode from inserted ins);
	set @cInvName = (select inv.cInvName from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode);
	set @cInvStd = (select inv.cInvStd from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode);
	set @cComUnitCode = (select inv.cComUnitCode from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode);
	set @iInvSaleCost = (select ins.iInvSaleCost from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode);
	set @mainTb = 'priceMainTable_'+@cCusCode;
	set @listTb = 'priceListTable_'+@cCusCode;
	set @mainTableName = 'zzx_link.OA.dbo.'+'priceMainTable_'+@cCusCode;
	set @listTableName = 'zzx_link.OA.dbo.'+'priceListTable_'+@cCusCode;
	set @rowsCount = '-1';
	-- 存在即更新，否则新增（表头+表体）
	if EXISTS (SELECT  * 
				 FROM dbo.SysObjects 
				WHERE ID = object_id(N''''+@mainTb+'''') 
				  AND OBJECTPROPERTY(ID, 'IsTable') = 1)
		begin
			set @sqls='select @rowsCount = count(1) from '+@listTableName+' where field0004 = @cInvCode;'
			exec sp_executesql @sqls,N'@rowsCount int out,@cInvCode nvarchar(100)',@rowsCount out,@cInvCode;
			select @rowsCount;
			if @rowsCount != 0
				begin
					-- 表体：更新
					set @sql = N'update '+@listTableName+' set field0008 = @iInvSaleCost where field0004 = @cInvCode;'
					exec sp_executesql @sql,
								N'@iInvSaleCost nvarchar(100),@cInvCode nvarchar(100)',
							    @iInvSaleCost,@cInvCode;
				end
			else 
				begin
					-- 表体：新增
					set @sql = N'insert into '+@listTableName+' (ID,field0009,field0004,field0005,field0006,field0007,field0008,formmain_id)
							    values (isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10001),@AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost,11);';
					exec sp_executesql @sql,
								N'@AutoID nvarchar(100),@cInvCode nvarchar(100),@cInvName nvarchar(100),@cInvStd nvarchar(100),@cComUnitCode nvarchar(100),@iInvSaleCost nvarchar(100)',
							    @AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost;
				end
		end
	else
		BEGIN
			-- 建表，跨服务器问题
			--exec createPriceTable @mainTB,@listTB;
			-- 建表
			exec ('USE [OA]
				  CREATE TABLE '+@mainTB+'(
						ID numeric(19, 0) NOT NULL,
						state int NULL,
						start_member_id varchar(25) NULL,
						start_date datetime NULL,
						approve_member_id varchar(25) NULL,
						approve_date datetime NULL,
						finishedflag int NULL,
						ratifyflag int NULL,
						ratify_member_id varchar(25) NULL,
						ratify_date datetime NULL,
						sort int NULL,
						modify_member_id varchar(25) NULL,
						modify_date datetime NULL,
						field0001 nvarchar(255) NULL,
						field0002 nvarchar(255) NULL,
						field0003 nvarchar(255) NULL,
					PRIMARY KEY CLUSTERED 
					(
						ID ASC
					)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
					ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
					) ON [PRIMARY];') at zzx_link;

			exec ('USE [OA]
				  CREATE TABLE '+@listTB+'(
						ID numeric(19, 0) NOT NULL,
						formmain_id numeric(19, 0) NULL,
						sort int NULL,
						field0004 nvarchar(255) NULL,
						field0005 nvarchar(255) NULL,
						field0006 nvarchar(255) NULL,
						field0007 nvarchar(255) NULL,
						field0008 nvarchar(255) NULL,
						field0009 nvarchar(255) NULL,
					PRIMARY KEY CLUSTERED 
					(
						ID ASC
					)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
					ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
					) ON [PRIMARY];') at zzx_link;
			-- 表头：新增
			set @sql1 = N'insert into '+@mainTableName+' (id,field0001,field0002,field0003)
						select isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10000),20160125,@cCusCode,@cCusName
						 where not exists (select 1
											 from '+@mainTableName+');';
			exec sp_executesql @sql1,
						N'@cCusCode nvarchar(100),@cCusName nvarchar(100)',
						@cCusCode,@cCusName;
			
			---- 表体：新增
			--set @sql2 = N'insert into '+@listTableName+' (ID,field0009,field0004,field0005,field0006,field0007,field0008,formmain_id)
			--			values (isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10001),@AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost,11);';
			--exec sp_executesql @sql2,
			--			N'@AutoID nvarchar(100),@cInvCode nvarchar(100),@cInvName nvarchar(100),@cInvStd nvarchar(100),@cComUnitCode nvarchar(100),@iInvSaleCost nvarchar(100)',
			--			@AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost; 
			
		END
end
腾信软创 2016-01-29
  • 打赏
  • 举报
 回复
引用 5 楼 roy_88 的回复:
看提示信息，这类信息只会在并发时才会出现 你在触发器里用链接服务器，看你的方法，只要MSDTC配置正确，防火墙设置好或关闭，就会正常 下面的是源码，版主给看看吧
[code=sqldrop procedure createPriceTable; go -- 创建价格表：priceTable_01001(经销商编码) create procedure createPriceTable ( @mainTB nvarchar(100), @listTB nvarchar(100) ) as SET NOCOUNT ON BEGIN begin transaction -- 建表 exec ('USE [OA] CREATE TABLE '+@mainTB+'( ID numeric(19, 0) NOT NULL, state int NULL, start_member_id varchar(25) NULL, start_date datetime NULL, approve_member_id varchar(25) NULL, approve_date datetime NULL, finishedflag int NULL, ratifyflag int NULL, ratify_member_id varchar(25) NULL, ratify_date datetime NULL, sort int NULL, modify_member_id varchar(25) NULL, modify_date datetime NULL, field0001 nvarchar(255) NULL, field0002 nvarchar(255) NULL, field0003 nvarchar(255) NULL, PRIMARY KEY CLUSTERED ( ID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];') at zzx_link; exec ('USE [OA] CREATE TABLE '+@listTB+'( ID numeric(19, 0) NOT NULL, formmain_id numeric(19, 0) NULL, sort int NULL, field0004 nvarchar(255) NULL, field0005 nvarchar(255) NULL, field0006 nvarchar(255) NULL, field0007 nvarchar(255) NULL, field0008 nvarchar(255) NULL, field0009 nvarchar(255) NULL, PRIMARY KEY CLUSTERED ( ID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];') at zzx_link; IF @@TRANCOUNT>0 COMMIT TRAN end SET NOCOUNT OFF go drop TRIGGER JY_syncSA_CusUPrice_For_Update; go --价格表： CREATE TRIGGER JY_syncSA_CusUPrice_For_Update on SA_CusUPrice after update,insert as begin declare @mainTableName nvarchar(100); declare @listTableName nvarchar(100); declare @mainTb nvarchar(100); declare @listTb nvarchar(100); declare @rowsCount nvarchar(100); declare @sql nvarchar(2550); declare @sqls nvarchar(1000); declare @sql1 nvarchar(2550); declare @sql2 nvarchar(2550); declare @AutoID nvarchar(100); declare @cCusCode nvarchar(100); declare @cCusName nvarchar(100); declare @cInvCode nvarchar(100); declare @cInvName nvarchar(100); declare @cInvStd nvarchar(100); declare @cComUnitCode nvarchar(100); declare @iInvSaleCost nvarchar(100); set @cCusCode = (select cCusCode from inserted ins); set @cCusName = (select cus.cCusName from inserted ins,Customer cus where ins.cCusCCode = cus.cCusCode); set @AutoID = (select ins.AutoID from inserted ins); set @cInvCode = (select ins.cInvCode from inserted ins); set @cInvName = (select inv.cInvName from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode); set @cInvStd = (select inv.cInvStd from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode); set @cComUnitCode = (select inv.cComUnitCode from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode); set @iInvSaleCost = (select ins.iInvSaleCost from inserted ins,inventory inv where ins.cInvCode = inv.cInvCode); set @mainTb = 'priceMainTable_'+@cCusCode; set @listTb = 'priceListTable_'+@cCusCode; set @mainTableName = 'zzx_link.OA.dbo.'+'priceMainTable_'+@cCusCode; set @listTableName = 'zzx_link.OA.dbo.'+'priceListTable_'+@cCusCode; set @rowsCount = '-1'; -- 存在即更新，否则新增（表头+表体） if EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N''''+@mainTb+'''') AND OBJECTPROPERTY(ID, 'IsTable') = 1) begin set @sqls='select @rowsCount = count(1) from '+@listTableName+' where field0004 = @cInvCode;' exec sp_executesql @sqls,N'@rowsCount int out,@cInvCode nvarchar(100)',@rowsCount out,@cInvCode; select @rowsCount; if @rowsCount != 0 begin -- 表体：更新 set @sql = N'update '+@listTableName+' set field0008 = @iInvSaleCost where field0004 = @cInvCode;' exec sp_executesql @sql, N'@iInvSaleCost nvarchar(100),@cInvCode nvarchar(100)', @iInvSaleCost,@cInvCode; end else begin -- 表体：新增 set @sql = N'insert into '+@listTableName+' (ID,field0009,field0004,field0005,field0006,field0007,field0008,formmain_id) values (isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10001),@AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost,11);'; exec sp_executesql @sql, N'@AutoID nvarchar(100),@cInvCode nvarchar(100),@cInvName nvarchar(100),@cInvStd nvarchar(100),@cComUnitCode nvarchar(100),@iInvSaleCost nvarchar(100)', @AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost; end end else BEGIN -- 建表，跨服务器问题 --exec createPriceTable @mainTB,@listTB; -- 建表 exec ('USE [OA] CREATE TABLE '+@mainTB+'( ID numeric(19, 0) NOT NULL, state int NULL, start_member_id varchar(25) NULL, start_date datetime NULL, approve_member_id varchar(25) NULL, approve_date datetime NULL, finishedflag int NULL, ratifyflag int NULL, ratify_member_id varchar(25) NULL, ratify_date datetime NULL, sort int NULL, modify_member_id varchar(25) NULL, modify_date datetime NULL, field0001 nvarchar(255) NULL, field0002 nvarchar(255) NULL, field0003 nvarchar(255) NULL, PRIMARY KEY CLUSTERED ( ID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];') at zzx_link; exec ('USE [OA] CREATE TABLE '+@listTB+'( ID numeric(19, 0) NOT NULL, formmain_id numeric(19, 0) NULL, sort int NULL, field0004 nvarchar(255) NULL, field0005 nvarchar(255) NULL, field0006 nvarchar(255) NULL, field0007 nvarchar(255) NULL, field0008 nvarchar(255) NULL, field0009 nvarchar(255) NULL, PRIMARY KEY CLUSTERED ( ID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];') at zzx_link; -- 表头：新增 set @sql1 = N'insert into '+@mainTableName+' (id,field0001,field0002,field0003) select isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10000),20160125,@cCusCode,@cCusName where not exists (select 1 from '+@mainTableName+');'; exec sp_executesql @sql1, N'@cCusCode nvarchar(100),@cCusName nvarchar(100)', @cCusCode,@cCusName; ---- 表体：新增 --set @sql2 = N'insert into '+@listTableName+' (ID,field0009,field0004,field0005,field0006,field0007,field0008,formmain_id) -- values (isnull((SELECT MAX(ID)+1 FROM '+@listTableName+'),10001),@AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost,11);'; --exec sp_executesql @sql2, -- N'@AutoID nvarchar(100),@cInvCode nvarchar(100),@cInvName nvarchar(100),@cInvStd nvarchar(100),@cComUnitCode nvarchar(100),@iInvSaleCost nvarchar(100)', -- @AutoID,@cInvCode,@cInvName,@cInvStd,@cComUnitCode,@iInvSaleCost; END end[/code]
中国风 2016-01-29
  • 打赏
  • 举报
 回复
看提示信息，这类信息只会在并发时才会出现 你在触发器里用链接服务器，看你的方法，只要MSDTC配置正确，防火墙设置好或关闭，就会正常
腾信软创 2016-01-29
  • 打赏
  • 举报
 回复
引用 2 楼 roy_88 的回复:
在触发器结尾加上试试 IF @@TRANCOUNT>0 COMMIT TRAN 或 放在触 发器里 
 -- 建表
    exec ('CREATE TABLE a') at zzx_link;
 
    exec ('CREATE TABLE b') at zzx_link;
加commit tran后还是不行； 用动态实现是因为 1.跨服务器操作 2.表名是字符串拼接后赋值给变量，然后跨服务器新建表/CRUD表中的数据
如何使用SQL语句创建触发器
存储过程可以通过语句直接调用,而 触发器主要是通过事件进行触发而被执行的. 例如当对某一表进行诸如`UPDATE`(修改)、`INSERT`(插入)、`DELETE`(删除)这些操作时，SQL Server 就会自动执行触发器所定义的`SQL`语句，...
sql 触发器 详解与实例
触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些...
mysql触发器简介、创建触发器及使用限制分析
SQL触发器是存储在数据库目录中的一组SQL语句。每当与表相关联的事件发生时，即会执行或触发SQL触发器，例如插入，更新或删除。SQL触发器也可以当做是一种特殊类型的存储过程。 它是特别的，因为它不像直接像存储...
SQL SERVER 触发器介绍
触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。...SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有creat
创建存储过程与触发器实验
存储过程可以使得程序执行效率更高、安全性更好，因为过程建立之后 已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，直接写sql语句会带来安全性问题，如：sql注入 建立过程不会很耗系统...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章