触发器中动态生成insert into 语句的问题？

[JCJC错别字检测-田春峰]

大家好：
我碰到的问题如下：
触发器中动态生成insert into 语句的问题？
具体是：因为sql server中字符串包括在：''中。
可是我需要在触发其中动态生成,类似下面的语句：
insert into ( col1 , col2 ) values( 'aaa' , 'bbb' )
insert into ( col1 , col2 ) values( 'eee' , 'fff' )
insert into ( col1 , col2 ) values( 'kkk' , 'mmm' )
的语句。

可是 sql server 不能用 ''aaa'' 这样的句子在来生成一个字符串。
如果我用变量方法代替：Declare @aaa = 'aaa'等等,然后使用：
insert into ( col1 , col2 ) values( @aaa , @bbb )
insert into ( col1 , col2 ) values( @eee , @fff )
可是sql把上面的语句解释成：
insert into ( col1 , col2 ) values( aaa , bbb )
insert into ( col1 , col2 ) values( eee , fff )

老天：我怎么动态生成insert into呀
能给我例子吗

[CrazyFor]

Declare @aaa varchar(1000)
set @aaa= 'aaa'

这样定义变量。

[CrazyFor]

动态生成,那么你需要把一个单引号替换成两个单引号，如：

insert into ( col1 , col2 ) values( ''kkk'' , ''mmm'' )

当使用单引号分隔一个包括嵌入单引号的字符常量时，用两个单引号表示嵌入单引号，例如：

SET @MyCharVar = 'O''Leary'

[JCJC错别字检测-田春峰]

CrazyFor(蚂蚁) (

你好：

比如：
Declare @a varchar(100)
set @a = newid() -- 假设：newid() ==1868C7D5-46F9-40C0-8053-E412D2B3DC98
sql Server把 insert into ( col1 , col2 ) values( @a, 'bbb' )
翻译成：insert into ( col1 , col2 )
values( 1868C7D5-46F9-40C0-8053-E412D2B3DC98 , 'bbb' )
而不是：
insert into ( col1 , col2 )
values( '1868C7D5-46F9-40C0-8053-E412D2B3DC98' , 'bbb' )

[pengdali]

触发器中：

insert 目标表 select ''''+col1+'''',''''+col2+'''' from inserted

[pengdali]

触发器中：

insert 目标表 select 'insert 表 values('''+col1+''','''+col2+''') ;' from inserted

[JCJC错别字检测-田春峰]

CrazyFor(蚂蚁) (

你振兴
牛呀：

我解决了


给分

USE trlanmao
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'TRInsertOrderMainTable' AND type = 'TR')
DROP TRIGGER TRInsertOrderMainTable
GO

Create Trigger TRInsertOrderMainTable On saleMain for INSERT
as
Declare @OldRelationShipId varchar(20)
Declare @NewRelationGuid varchar(100)
Declare @BiggestOrderNo varchar(20)
Declare @CustomerIdNo varchar(100)
Declare @CustomerNameNo varchar(20)
Declare @UserLoginName varchar(50)

DECLARE @IOrderNOlength int

--第一部分
-- 获得saleMain的关联OrderId
SET @OldRelationShipId = ( SELECT OrderId from INSERTED )
-- 获得代理的GUID
SET @UserLoginName = ( SELECT Uid from INSERTED )
SET @CustomerIdNo = ( SELECT UsersId From Scmis.dbo.Users WHERE UserName = @UserLoginName )
--print @CustomerIdNo
--获得用户的登陆名称
--
--SET @OldRelationShipId= @@IDENTITY

--获得11位的最大OrderNo
--
SELECT @BiggestOrderNo= MAX(scmis.dbo.[Order].OrderNo) FROM scmis.dbo.[Order]
SELECT @BiggestOrderNo = RIGHT( @BiggestOrderNo , 9 )
SELECT @BiggestOrderNo = @BiggestOrderNo + 1
SELECT @IOrderNOlength = len( @BiggestOrderNo )
WHILE @IOrderNOlength < 9
BEGIN
SELECT @BiggestOrderNo = '0'+@BiggestOrderNo
SELECT @IOrderNOlength = len( @BiggestOrderNo )
END
SELECT @BiggestOrderNo = 'DD'+@BiggestOrderNo
--End
--

--
Declare @TcfOrderMainAndDetailRelateGuid varchar (100)
SET @TcfOrderMainAndDetailRelateGuid = newid()
INSERT INTO scmis.dbo.[Order](
[OrderID], [OrderNo], [OriginalOrderNo],
[CustomerID],
[OrderDate],
[OrderTotalSum], [TotalAmount],
[ModifiedID],
[Note],
[Creator], [CreateDate],
[Modifier], [ModifyDate],
[Assessor], [AuditingDate],
[StatusFlag], [StatusDes],
[BalanceFlag], [SplitFlag], [AuditFlag]

)
VALUES( @TcfOrderMainAndDetailRelateGuid, @BiggestOrderNo , @OldRelationShipId ,
@CustomerIdNo ,
getdate(),
100,100 ,
newid(),
'并te',
'并行a', getdate(),
'并行b', getdate(),
'<S>', getdate(),
0,'<Sta',
0,0,0

)
PRINT '结束 插入 主表'
-- 第二部分插入到数据库中OrderDetail from saleDetail
Declare TcfDetailTablecursor Cursor
FOR SELECT
ProdCode ,
Prodname,
piece,
price,
num ,
[sum],
tot,
unity,
dprice,
zprice
from saleDetail Where OrderId = @OldRelationShipId
--
--

------------------------------------------------
DECLARE @TcfProductCode varchar(50)
DECLARE @TcfProdName varchar(50)
DECLARE @TcfPiece varchar(50)
DECLARE @TcfPrice varchar(50)
DECLARE @TcfNum varchar(50)
DECLARE @TcfSum varchar(50)
DECLARE @TcfTot varchar(50)
DECLARE @TcfUnity varchar(50)
DECLARE @TcfDprice varchar(50)
DECLARE @TcfZPrice varchar(50)
------------------------------------------------
DECLARE @TcfNewProductId VARCHAR(100)
DECLARE @TcfNewOrderDetailGuid varchar(100)
OPEN TcfDetailTablecursor
DECLARE @InsertSqls VARCHAR(8000)
SET @InsertSqls=' '
-- FETCH NEXT FROM TcfDetailTablecursor
FETCH NEXT FROM TcfDetailTablecursor INTO
@TcfProductCode,
@TcfProdName,
@TcfPiece,
@TcfPrice,
@TcfNum,
@TcfSum,
@TcfTot,
@TcfUnity,
@TcfDprice,
@TcfZPrice
PRINT '开始循环'
WHILE ( @@FETCH_STATUS=0 )
---
BEGIN
SET @TcfNewProductId = ( SELECT ProductId From Scmis.dbo.Product WHERE ProductCode = @TcfProductCode )
SELECT @TcfNewOrderDetailGuid = newid()
SET @TcfOrderMainAndDetailRelateGuid = '''' + @TcfOrderMainAndDetailRelateGuid + ''''
SET @TcfNewProductId = '''' + @TcfNewProductId + ''''
---
PRINT '-------------------*******************************'
print @TcfOrderMainAndDetailRelateGuid
print @TcfNewProductId
PRINT '-------------------*******************************'
SELECT @InsertSqls = 'INSERT INTO [Scmis].[dbo].[OrderDetail]'
+ '('
+ '[OrderDetailID], [OrderID], [ProductID],'
+ '[PackageName], '
+ '[UnitAmount], [UnitName], '
+ '[InterAmount], [AmountUnit], '
+ '[ProductPrice], [SaleAmount], [OrderAmount],'
+ '[OutAmount], [Sum], '
+ '[Note], [Creator], [CreateDate], '
+ '[Modifier], [ModifyDate], '
+ '[StatusFlag], [SplitAmount])'
+ 'VALUES( NEWID(),' + @TcfOrderMainAndDetailRelateGuid+','+ @TcfNewProductId+','
+'paceagename,'
+'100,'+'unitname,'
+'200,'+'300,'
+'400,'+'500,''600,'
+'0,'+'0,' --sum
+'note,'+'creator,getdate(),'
+'modifier,getdate(),'
+'0,0 )'
print 'ddd'
PRINT @InsertSqls
print 'eee'
IF @InsertSqls IS NOT NULL
BEGIN
EXec (@InsertSqls)
END
ELSE
BEGIN
RAISERROR ('插入oRDERdETAIL表 的 时候出错，可能是因为没有找到匹配的产品编码',16,1)
END

--FETCH NEXT FROM TcfDetailTablecursor
FETCH NEXT FROM TcfDetailTablecursor INTO @TcfProductCode,
@TcfProdName,
@TcfPiece,
@TcfPrice,
@TcfNum,
@TcfSum,
@TcfTot,
@TcfUnity,
@TcfDprice,
@TcfZPrice
END
PRINT '结束循环'
-- print @InsertSqls
CLOSE TcfDetailTablecursor
DEALLOCATE TcfDetailTablecursor
SELECT @NewRelationGuid=@@IDENTITY
go