seoutstock 是表头
seoutstockentry 是表体因为程序是先行执行往seoutstockentry这张表中 插入(1行或多行)数据,然后再往seoutstock表插入数据 ,所以不起作用,因往seoutstockentry表中插入数据时,seoutstock对应的数据还不存在。现在可能要在seoutstock表中创建insert触发器,以下触发器怎么改写呢?
CREATE TRIGGER tigger20130828 ON seoutstockentry
FOR INSERT
AS
DECLARE @FCustID INT ,
@FItemID INT SELECT @FCustID = fcustid ,
@FItemID = fitemid
FROM inserted t1 ,
seoutstock t2
WHERE t1.finterid = t2.finterid IF EXISTS ( SELECT TOP 1 --暂时看来,只可能是这个EXISTS没有查询出来数据.楼主测试一下。
fcustid ,
fitemid FAuxTaxPrice
FROM ICSaleEntry u1
LEFT JOIN ICSale v1 ON u1.FInterID = v1.FInterID
WHERE fcustid = @FCustID
AND fitemid = @FItemID
AND FAuxTaxPrice > 0
AND FStatus = 1
AND FCancellation = 0
ORDER BY v1.FDate DESC ,
u1.FInterID DESC ,
u1.FEntryID DESC )
--UPDATE w2
--SET w2.fauxprice = w4.fauxprice
SELECT * --测试时,把UPDATE语句改成SELECT语句
FROM seoutstock w1 ,
seoutstockentry w2 ,
inserted w3 ,
( SELECT TOP 1
fcustid ,
fitemid ,
fauxprice
FROM ICSaleEntry u1
LEFT JOIN ICSale v1 ON u1.FInterID = v1.FInterID
WHERE fcustid = @FCustID
AND fitemid = @FItemID
AND FAuxTaxPrice > 0
AND FStatus = 1
AND FCancellation = 0
ORDER BY v1.FDate DESC ,
u1.FInterID DESC ,
u1.FEntryID DESC
) w4
WHERE w1.finterid = w2.finterid
AND w2.finterid = w3.finterid
AND w2.fentryid = w3.fentryid
AND w1.fcustid = w4.fcustid
AND w2.fitemid = w4.fitemid
seoutstockentry 是表体因为程序是先行执行往seoutstockentry这张表中 插入(1行或多行)数据,然后再往seoutstock表插入数据 ,所以不起作用,因往seoutstockentry表中插入数据时,seoutstock对应的数据还不存在。现在可能要在seoutstock表中创建insert触发器,以下触发器怎么改写呢?
CREATE TRIGGER tigger20130828 ON seoutstockentry
FOR INSERT
AS
DECLARE @FCustID INT ,
@FItemID INT SELECT @FCustID = fcustid ,
@FItemID = fitemid
FROM inserted t1 ,
seoutstock t2
WHERE t1.finterid = t2.finterid IF EXISTS ( SELECT TOP 1 --暂时看来,只可能是这个EXISTS没有查询出来数据.楼主测试一下。
fcustid ,
fitemid FAuxTaxPrice
FROM ICSaleEntry u1
LEFT JOIN ICSale v1 ON u1.FInterID = v1.FInterID
WHERE fcustid = @FCustID
AND fitemid = @FItemID
AND FAuxTaxPrice > 0
AND FStatus = 1
AND FCancellation = 0
ORDER BY v1.FDate DESC ,
u1.FInterID DESC ,
u1.FEntryID DESC )
--UPDATE w2
--SET w2.fauxprice = w4.fauxprice
SELECT * --测试时,把UPDATE语句改成SELECT语句
FROM seoutstock w1 ,
seoutstockentry w2 ,
inserted w3 ,
( SELECT TOP 1
fcustid ,
fitemid ,
fauxprice
FROM ICSaleEntry u1
LEFT JOIN ICSale v1 ON u1.FInterID = v1.FInterID
WHERE fcustid = @FCustID
AND fitemid = @FItemID
AND FAuxTaxPrice > 0
AND FStatus = 1
AND FCancellation = 0
ORDER BY v1.FDate DESC ,
u1.FInterID DESC ,
u1.FEntryID DESC
) w4
WHERE w1.finterid = w2.finterid
AND w2.finterid = w3.finterid
AND w2.fentryid = w3.fentryid
AND w1.fcustid = w4.fcustid
AND w2.fitemid = w4.fitemid
seoutstockentry 是表体因为程序是先行执行往seoutstockentry这张表中 插入(1行或多行)数据,然后再往seoutstock表插入数据 ,所以不起作用,因往seoutstockentry表中插入数据时,seoutstock对应的数据还不存在。现在可能要在seoutstock表中创建insert触发器,以下触发器怎么改写呢?
CREATE TRIGGER tigger20130828 ON seoutstockentry
FOR INSERT
AS
DECLARE @FCustID INT ,
@FItemID INT SELECT @FCustID = fcustid ,
@FItemID = fitemid
FROM inserted t1 ,
seoutstock t2
WHERE t1.finterid = t2.finterid IF EXISTS ( SELECT TOP 1 --暂时看来,只可能是这个EXISTS没有查询出来数据.楼主测试一下。
fcustid ,
fitemid FAuxTaxPrice
FROM ICSaleEntry u1
LEFT JOIN ICSale v1 ON u1.FInterID = v1.FInterID
WHERE fcustid = @FCustID
AND fitemid = @FItemID
AND FAuxTaxPrice > 0
AND FStatus = 1
AND FCancellation = 0
ORDER BY v1.FDate DESC ,
u1.FInterID DESC ,
u1.FEntryID DESC )
UPDATE w2
SET w2.fauxprice = w4.fauxprice
--SELECT * --测试时,把UPDATE语句改成SELECT语句
FROM seoutstock w1 ,
seoutstockentry w2 ,
inserted w3 ,
( SELECT TOP 1
fcustid ,
fitemid ,
fauxprice
FROM ICSaleEntry u1
LEFT JOIN ICSale v1 ON u1.FInterID = v1.FInterID
WHERE fcustid = @FCustID
AND fitemid = @FItemID
AND FAuxTaxPrice > 0
AND FStatus = 1
AND FCancellation = 0
ORDER BY v1.FDate DESC ,
u1.FInterID DESC ,
u1.FEntryID DESC
) w4
WHERE w1.finterid = w2.finterid
AND w2.finterid = w3.finterid
AND w2.fentryid = w3.fentryid
AND w1.fcustid = w4.fcustid
AND w2.fitemid = w4.fitemid
建议:先插主表,再插从表(同时会触发触发器)
[code=sql]select *
from sysobjects
where xtype='TR'[/code]
当表头数据有INSERT时,再去更新seoutstockentry表。