代码之家 › 专栏 › 技术社区 › Nitrodist

TSQL触发器未保存变量和/或未正确执行

tsql sql-server

Nitrodist · 技术社区 · 14 年前

我无法让TSQL触发器正常工作。我在调试器中运行了它,但它没有设置任何根据sqlservermanagementstudio的变量。最糟糕的是触发器本身执行正确,执行时没有错误(只是说“执行成功”)。

代码如下(这是一个正在进行的工作。。。。只是让我自己熟悉一下):

USE TestDb

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

CREATE TRIGGER OfficeSalesQuotaUpdate
ON SalesReps
AFTER UPDATE, DELETE, INSERT 
AS
    DECLARE @sales_difference int,  @quota_difference int
    DECLARE @sales_original int,    @quota_original int
    DECLARE @sales_new int,         @quota_new int

    DECLARE @officeid int
    DECLARE @salesrepid int

    --UPDATE(Sales) returns true for INSERT and UPDATE.
    --Not for DELETE though.    

    IF ((SELECT COUNT(*) FROM inserted) = 0)
        SET @salesrepid = (SELECT SalesRep FROM deleted)
    ELSE    
        SET @salesrepid = (SELECT SalesRep FROM inserted)   

    --If you address the @salesrepid variable, it does not work. Doesn't even 
    --print out the 'this should work line.
    PRINT 'This should work...' --+ convert(char(30), @salesrepid)

    IF (@salesrepid = NULL)
        PRINT 'SalesRepId is null'
    ELSE
        PRINT 'SalesRepId is not null'

    PRINT convert(char(50), @salesrepid)


    SET @officeid = (SELECT RepOffice 
                       FROM SalesReps 
                      WHERE SalesRep = @salesrepid)

    SELECT @sales_original =    (SELECT Sales FROM deleted)
    SELECT @sales_new =         (SELECT Sales FROM inserted)

    --Sales can not be null, so we'll remove this later.
    --Use this as a template for quota though, since that can be null.
    IF (@sales_new = null) 
    BEGIN
        SET @sales_new = 0
    END

    IF (@sales_original = 0)
    BEGIN
        SET @sales_original = 0
    END

    SET @sales_difference = @sales_new - @sales_original

    UPDATE Offices
    SET Sales = Sales + @sales_difference
    WHERE Offices.Office = @officeid
GO

有什么建议吗?这件事我完全搞砸了。提前谢谢。

3 回复 | 直到 14 年前

Pondlife 14 年前

你的主要问题似乎是 @foo = NULL 和 @foo IS NULL :

declare @i int
set @i = null -- redundant, but explicit

if @i = null print 'equals'
if @i is null print 'is'

“This should work”PRINT语句不起作用,因为将空值与字符串连接会产生空值,并且 PRINT NULL 什么都没印。

至于实际设置@salerepid的值,很可能插入和/或删除的表实际上是空的。你用什么语句来测试触发器?你把计数值打印出来了吗?

您还应该考虑(如果您还没有)如果有人同时更改多行,会发生什么情况。您当前的代码假设一次只更改一行,这在您的环境中可能是一个合理的假设,但如果有人批量加载数据或执行其他“批处理”,则很容易中断。

最后,您应该总是提到您的MSSQL版本和版本;它可能与一些语法问题有关。

Damien_The_Unbeliever 14 年前

你应该用这样的东西替换触发器的主体:

;WITH Totals AS (
     SELECT RepOffice,SUM(Sales) as Sales FROM inserted GROUP BY RepOffice
     UNION ALL
     SELECT RepOffice,-SUM(Sales) FROM deleted GROUP BY RepOffice
), SalesDelta AS (
     SELECT RepOffice,SUM(Sales) as Delta FROM Totals GROUP BY RepOffice
)
UPDATE o
SET Sales = Sales + sd.Delta
FROM
    Offices o
        inner join
    SalesDelta sd
        on
             o.Office = sd.RepOffice

这将充分处理插入和删除中的多行。我假设SalesRep是SalesReps表的主键。

上面更新了,以应对更新更改了某个特定销售代表的报告(原报告可能也不正确)

bosvos 14 年前

只是个建议…你有没有试过把开始和结束封装成触发器的一部分?