写了触发器不起作用，哪位高手能帮小弟我看一下有关问题出在哪

2013-09-05

写了触发器不起作用，谁能帮我看一下问题出在哪create trigger tigger20130828 onseoutstockentryfor inser

写了触发器不起作用，谁能帮我看一下问题出在哪
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 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 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

[解决办法]

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

[解决办法]
判断是否存在，只要这样写就可以了

if  EXISTS 
(select 1 from ICSaleEntry u1
 inner join ICSale v1 on u1.FInterID=v1.FInterID
 where 
fcustid=@FCustID and fitemid=@FItemID
and FAuxTaxPrice>0 and FStatus=1 and FCancellation=0 
)

注意用inner join ，不能用left join

热点排行

Bad Request.

SQL Server

写了触发器不起作用，哪位高手能帮小弟我看一下有关问题出在哪