在SQL中创建好的TRIGGER 触发器 功能执行没问题但是有时候会自动消失,请问这个是为什么
话不多说,带上源码,看看是不是代码的问题。
create TRIGGER TRIGGER_PaiPin
ON AuctionRecord FOR INSERT
AS
--用户编号 代理出价 加价幅度 当前最高价格 当前设置代理出价数量 拍品编号 最后一个出价人ID 最后一个人出价商品
DECLARE @UserID INT,@Price MONEY,@fudu MONEY,@dqmoney MONEY,@proxyCount INT,@paoductid INT,@lastUserID INT,@lastproductid INT
--查询有效 设置代理出价的用户的个数
SELECT @proxyCount=count(1) FROM ProxyRecord p INNER JOIN paipinInfo pi ON pi.Id=p.productId
WHERE p.flat1=0 AND pi.isshenhe=3
IF @proxyCount>0
BEGIN
WHILE @proxyCount>0
BEGIN
SELECT @paoductid=productid,@UserID=userid,@Price=price FROM(
SELECT row_number() OVER(ORDER BY id asc) AS rowid,id,productid,userid,price
FROM ProxyRecord WHERE flat1=0 and productID IN(
SELECT id FROM paipinInfo WHERE isshenhe IN (3) AND flat1=0 AND (remark2 IS NULL OR remark2<>'1')
)) AS D WHERE rowid=@proxyCount
IF @UserID>0 AND @Price>0
BEGIN
--找到最后一个出价的商品编号
SELECT TOP 1 @lastproductid=productid FROM AuctionRecord ORDER BY id DESC
IF @lastproductid=@paoductid
BEGIN
--PRINT convert(NVARCHAR(20),@Price)+' '+convert(NVARCHAR(20),@UserID)+' '+convert(NVARCHAR(20),@paoductid)
--过滤掉最后一个出价
SELECT TOP 1 @lastUserID=userid FROM AuctionRecord WHERE productID=productid ORDER BY Id DESC
IF @lastUserID<>@UserID
BEGIN
SELECT @fudu=fudu FROM paipinInfo WHERE Id=@paoductid
SELECT @dqmoney=max(price) FROM AuctionRecord WHERE flat1=0 AND productId=@paoductid
IF @dqmoney<@Price
BEGIN
INSERT INTO dbo.AuctionRecord (userid,productId,price,addtime,flat1,flat2)
VALUES (@UserID,@paoductid,@dqmoney+@fudu,getdate(),0,0)
END
END
END
END
SET @proxyCount=@proxyCount-1
END
END
GO