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
