对象名称 [dbo] 无效.[触发器名称]

Question

我创建了一个名为 UPDATE_MERCHANDISE 的触发器。查询完成时出现一个错误，即一个简单的拼写错误。我修复了拼写错误并将代码更改为ALTER TRIGGER，但现在当我尝试执行它时，我收到错误

对象名称 [dbo] 无效。[UPDATE_MERCHANDISE]

为什么它在我创建它时有效，而在我尝试更改它时无效？任何和所有的帮助/想法将不胜感激。

谢谢。

USE [CIS31038]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UPDATE_MERCHANDISE] ON [dbo].[ORDERITEM]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE @ITEMID VARCHAR(2)
DECLARE @TOTAL INT
--INSERT CASE
IF(EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE INSERT_CURSOR CURSOR FOR
SELECT ITEMID, SUM(QUANTITY) AS TOTAL
FROM INSERTED
GROUP BY ITEMID
OPEN INSERT_CURSOR
FETCH NEXT FROM INSERT_CURSOR INTO @ITEMID, @TOTAL
WHILE(@@FETCH_STATUS = 0)
BEGIN
    UPDATE MERCHANDISE
    SET QUANTITYONHAND = QUANTITYONHAND - @TOTAL
    WHERE ITEMID = @ITEMID
    FETCH NEXT FROM INSERT_CURSOR INTO @ITEMID, @TOTAL
END
CLOSE INSERT_CURSOR
DEALLOCATE INSERT_CURSOR
END
--DELETE CASE
IF(EXISTS (SELECT * FROM DELETED) AND NOT EXISTS (SELECT * FROM INSERTED))
BEGIN
DECLARE DELETE_CURSOR CURSOR FOR
SELECT ITEMID, SUM(QUANTITY) AS TOTAL
FROM DELETED
GROUP BY ITEMID
OPEN DELETE_CURSOR
FETCH NEXT FROM DELETE_CURSOR INTO @ITEMID, @TOTAL
WHILE (@@FETCH_STATUS = 0)
BEGIN
    UPDATE MERCHANDISE
    SET QUANTITYONHAND = QUANTITYONHAND + @TOTAL
    WHERE ITEMID = @ITEMID
    FETCH NEXT FROM DELETE_CURSOR INTO @ITEMID, @TOTAL
END
CLOSE DELETE_CURSOR
DEALLOCATE DELETE_CURSOR
END
--UPDATE CASE
IF(EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED))
BEGIN
DECLARE UPDATE_CURSOR CURSOR FOR
SELECT I.ITEMID, SUM(I.QUANTITY - D.QUANTITY) AS TOTAL
FROM INSERTED I INNER JOIN DELETED D ON
     I.PONUMBER = D.PONUMBER AND I.ITEMID = D.ITEMID
GROUP BY I.ITEMID
OPEN UPDATE_CURSOR
FETCH NEXT FROM UPDATECURSOR INTO @ITEMID, @TOTAL
WHILE (@@FETCH_STATUS = 0)
BEGIN
    UPDATE MERCHANDISE
    SET QUANTITYONHAND = QUANTITYONHAND - @TOTAL
    WHERE ITEMID = @ITEMID
    FETCH NEXT FROM UPDATE_CURSOR INTO @ITEMID, @TOTAL
END
CLOSE UPDATE_CURSOR
DEALLOCATE UPDATE_CURSOR
END
END

Answer 1

如果您有拼写错误，很可能触发器从未创建过

Try CREATE TRIGGER instead of ALTER TRIGGER

顺便说一下，一般来说，你删除触发器并重新创建，使用类似的东西。

IF Object_id('TRIGGER_NAME') is not null
    DROP TRIGGER Trigger Name
GO
CREATE TRIGGER TriggerName
AS