July 13, 2015 at 8:12 am
My scenario: I have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.
If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.
To recreate the issue:
CREATE DATABASE DbWithTrigger
GO
USE DbWithTrigger
GO
CREATE TABLE dbo.TblWithTrigger(
Id int NULL,
SomeValue varchar(30) NULL
)
GO
CREATE TRIGGER MyTrigger
ON dbo.TblWithTrigger
FOR INSERT
AS
BEGIN
INSERT TblWithTrigger (Id, SomeValue)
SELECT Id + 1, SomeValue
FROM inserted
END
GO
-- Test to confirm working trigger
INSERT TblWithTrigger VALUES(1, 'Blah blah')
GO
SELECT * FROM dbo.TblWithTrigger
GO
CREATE DATABASE DbCallsTrigger
GO
USE DbCallsTrigger
GO
CREATE PROCEDURE CrossDbTriggerCall
AS
ALTER TABLE DbWithTrigger.dbo.TblWithTrigger DISABLE TRIGGER MyTrigger
GO
-- Test to confirm
EXEC CrossDbTriggerCall
INSERT DbWithTrigger.dbo.TblWithTrigger VALUES(1, 'Blah blah')
In Visual Studio 2012:
1. Create a new solution with a project named DbWithTrigger
2. In project settings set the Target platform to SQL 2012
2. Import the DbWithTrigger db into this project
3. Create a new project named DbCallsTrigger
4. In project settings set the Target platform to SQL 2012
5. Import the DbCallsTrigger db into this project
6. Add a Database Reference in DbCallsTrigger for DbWithTrigger
When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Anyone else seen this? Is it a bug?
I've run out of ideas how to troubleshoot and found nothing online.
July 15, 2015 at 4:28 am
Hmmm.. very quiet here on this subject - only 18 views in 2 days and I think four or five of those are me.
Oh well, fortunately somebody answered this for me here:
As stated there DISABLE TRIGGER [$(DbWithTrigger)].[dbo].[MyTrigger] on [$(DbWithTrigger)].[dbo].[TblWithTrigger]; works - the previous statement must have a semi-colon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply