在VS2010中从sql文件创建触发器?

发布于 2025-01-06 03:23:59 字数 808 浏览 2 评论 0原文

我这里有一个奇怪的问题,我想创建一个空触发器:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.TestTrigger 
   ON  _TestDB.dbo.test
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
END
GO

如果我在 SSMS 中执行它,它可以正常工作,但是如果我在 Visual Studio 中创建一个新的 SQL 文件并单击“执行 SQL”,那么我只会收到一条错误消息:

消息 2108,级别 15,状态 1,过程 TestTrigger,第 13 行 Erstellen 触发 kann nicht für '_TestDB.dbo.test' ausgeführt werden, da sich das Ziel nicht in der aktuellen Datenbank befindet。

翻译:

消息 2108,级别 15,状态 1,过程 TestTrigger,第 13 行创建 无法对“_TestDB.dbo.test”执行触发器,因为 当前数据库中不存在目标。

SSMS和VS2010都以同一用户连接到同一数据库服务器。

简单的 Select * From _TestDB.dbo.test 在 Visual Studio 中确实可以工作,因此与数据库的连接应该可以工作。但为什么它不适用于“创建触发器”?

I have a strange problem here, I want to create an emtpy trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.TestTrigger 
   ON  _TestDB.dbo.test
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
END
GO

If I execute this within SSMS it works perfectly, but if I create a new SQL file within Visual Studio and click "Execute SQL", then I just get an error message:

Msg 2108, Level 15, State 1, Procedure TestTrigger, Line 13 Erstellen
Trigger kann nicht für '_TestDB.dbo.test' ausgeführt werden, da sich
das Ziel nicht in der aktuellen Datenbank befindet.

Translation:

Msg 2108, Level 15, State 1, Procedure TestTrigger, Line 13 Create
Trigger cannot be executed for '_TestDB.dbo.test', because
the target doesn't exist in the current database.

SSMS and VS2010 are both connected to the same databaseserver as the same user.

A simple Select * From _TestDB.dbo.test does work within Visual Studio, so the connection to the database should work. But why is it not working for Create Trigger?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

芸娘子的小脾气 2025-01-13 03:23:59

正如错误所暗示的那样,触发器必须在与基础目标表相同的数据库中创建。

因此,请确保您在与 dbo.test 表相同的数据库 (_TestDB) 中创建触发器

USE _TestDB
GO

SET ANSI_NULLS ON GO 
SET QUOTED_IDENTIFIER ON 
GO  

CREATE TRIGGER dbo.TestTrigger     
ON  dbo.test    
AFTER UPDATE AS  
BEGIN     
  SET NOCOUNT ON; 
END 
GO 

编辑:只是为了澄清 - 连接位于服务器实例级别,而不是数据库级别。通过将表限定为 _TestDB.dbo.test,即使您当前的数据库(目录)当前指向不同的数据库,您也将能够访问该表。

编辑:OIC - 是的,这意味着您当前的数据库不是“_TestDB”。您还发现 SQL 的 DDL 执行存在不一致问题。大多数DDL命令可以从远程数据库成功执行(包括CREATE INDEX),但由于某种原因,不能通过触发器执行。下面重点介绍一下不一致之处(SQL 2008 Express)

use master
go

CREATE DATABASE bob
GO

-- DB_NAME() = master
CREATE TABLE bob.dbo.SomeTable
(
    SomeTableId INT NOT NULL,
    AnotherField VARCHAR(50) NULL
)
GO -- Success

-- DB_NAME() = master
CREATE INDEX IX1_SomeTable on bob.dbo.SomeTable(SomeTableId)
GO -- Success. Note that the index is actually created in bob, not master of course

-- DB_NAME() = master
ALTER TABLE bob.dbo.SomeTable ADD CONSTRAINT PK_SomeTableId PRIMARY KEY(SomeTableId)
GO -- Success

-- DB_NAME() = master
CREATE TRIGGER bob.dbo.SomeTableTrigger -- 'CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.
    ON  bob.dbo.SomeTable
    AFTER UPDATE
AS
    BEGIN
        PRINT 'Trigger called'
    END
GO

-- DB_NAME() = master - this is your scenario - 
CREATE TRIGGER dbo.SomeTableTrigger -- Cannot create trigger on 'bob.dbo.SomeTable' as the target is not in the current database.
    ON  bob.dbo.SomeTable
    AFTER UPDATE
AS
    BEGIN
        PRINT 'Trigger called'
    END
GO

USE BOB
GO

-- DB_NAME() = bob
CREATE TRIGGER dbo.SomeTableTrigger -- Success
    ON  bob.dbo.SomeTable
    AFTER UPDATE
AS
    BEGIN
        PRINT 'Trigger called'
    END
GO

As the error implies, triggers must be created in the same database as the underlying target table.

So ensure that you create the trigger in the same DB (_TestDB) as your dbo.test table

USE _TestDB
GO

SET ANSI_NULLS ON GO 
SET QUOTED_IDENTIFIER ON 
GO  

CREATE TRIGGER dbo.TestTrigger     
ON  dbo.test    
AFTER UPDATE AS  
BEGIN     
  SET NOCOUNT ON; 
END 
GO 

Edit : Just to clarify - a connection is at server instance level, not at database level. By qualifying your table as _TestDB.dbo.test you will be able to access the table even if your current database (catalogue) is currently pointing to a different database.

Edit : OIC - yes, it means that your current db isn't "_TestDB". You've also picked up an inconsistency in SQL's DDL execution. Most of the DDL commands can be successfully executed from a remote database (Including CREATE INDEX), but for some reason, not a trigger. The following highlights the inconsistency (SQL 2008 Express)

use master
go

CREATE DATABASE bob
GO

-- DB_NAME() = master
CREATE TABLE bob.dbo.SomeTable
(
    SomeTableId INT NOT NULL,
    AnotherField VARCHAR(50) NULL
)
GO -- Success

-- DB_NAME() = master
CREATE INDEX IX1_SomeTable on bob.dbo.SomeTable(SomeTableId)
GO -- Success. Note that the index is actually created in bob, not master of course

-- DB_NAME() = master
ALTER TABLE bob.dbo.SomeTable ADD CONSTRAINT PK_SomeTableId PRIMARY KEY(SomeTableId)
GO -- Success

-- DB_NAME() = master
CREATE TRIGGER bob.dbo.SomeTableTrigger -- 'CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.
    ON  bob.dbo.SomeTable
    AFTER UPDATE
AS
    BEGIN
        PRINT 'Trigger called'
    END
GO

-- DB_NAME() = master - this is your scenario - 
CREATE TRIGGER dbo.SomeTableTrigger -- Cannot create trigger on 'bob.dbo.SomeTable' as the target is not in the current database.
    ON  bob.dbo.SomeTable
    AFTER UPDATE
AS
    BEGIN
        PRINT 'Trigger called'
    END
GO

USE BOB
GO

-- DB_NAME() = bob
CREATE TRIGGER dbo.SomeTableTrigger -- Success
    ON  bob.dbo.SomeTable
    AFTER UPDATE
AS
    BEGIN
        PRINT 'Trigger called'
    END
GO
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文