无效的对象名称[dbo].[TriggerName] [英] Invalid object name [dbo].[TriggerName]

问题描述

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

I created a trigger named UPDATE_MERCHANDISE. The query completed with one error, a simple typo. I fixed the typo and changed the code to ALTER TRIGGER, but now when I try to execute it I receive the error

无效的对象名称[dbo].[UPDATE_MERCHANDISE]

Invalid object name [dbo].[UPDATE_MERCHANDISE]

为什么在创建它时有效，但在尝试更改它时无效?任何和所有帮助/想法将不胜感激.

Why would it be valid when I created it, but not when I try to alter it? Any and all help/ideas would be appreciated.

谢谢.

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

推荐答案

如果您有错字，很有可能永远不会创建触发器

If you had a typo, most likely, the trigger never got created

Try CREATE TRIGGER instead of ALTER TRIGGER

通常，您使用类似方法删除触发器并重新创建.

By the way, generally, you remove the trigger and recreate, using something like.

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

这篇关于无效的对象名称[dbo].[TriggerName]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！