Create trigger from a sql file within VS2010?

Question

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?

StuartLC · Accepted Answer · 2012-02-16 08:08:05Z

3

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

edited Feb 16, 2012 at 8:08

answered Feb 15, 2012 at 10:30

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Core_F Over a year ago

Thanks, that works. But I don't understand why. I thought with "ON _TestDB.dbo.test" I already tell him which database to use?

StuartLC Over a year ago

Think of a trigger as a 'listener' / subscriber to an event - it must be in the same db as the 'subject' to the action for which it is listening (viz updates on table dbo.Test). You can still use the 'local' trigger to insert data into a table in another database however.

Core_F Over a year ago

I must admit, that I don't get the difference technically, but I am not a big SQL-guy anyway. Always thought, that "USE _TestDB" tells him which database to use for this script, while "Create ... ON _TestDB.dbo.test" should tell him, that the trigger is in the _TestDB. But as my version is not working in SSMS either (if I change the database in the dropdown while editing the script), I guess I just have to accept, that I can't point to the database this way. Thanks again. :)

Collectives™ on Stack Overflow

Create trigger from a sql file within VS2010?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related