ALTER TRIGGER without schema

pil0t

ALTER TRIGGERS generates code, where trigger name used without schema name
Example:
ALTER TRIGGER [Validators_DeleteFromReferenceItems] ON [ref].[Validators]
FOR DELETE ...

this statement fails, because trigger name is [ref].[Validators_DeleteFromReferenceItems]

this bug is not repeated in SqlCompare (only in SDK), maybe I can fix it using some CompareOptions?

Anu D

Thank you for your post!

I tried to replicate the error at our end and SQL Compare also doesnt fully qualify the trigger if the definition is without the schema.

Can I ask you which version of SQL Compare and SQL Comparison SDK are you using?

pil0t

C:\Program Files\Red Gate\SQL Comparison SDK 8\Assemblies\SQL Data Compare\RedGate.SQLCompare.Engine.dll
7.1.0.182

C:\Program Files\Red Gate\SQL Comparison SDK 8\Assemblies\SQL Data Compare\RedGate.SQLDataCompare.Engine.dll
7.1.0.230

C:\Program Files\Red Gate\SQL Packager 6\RedGate.SQLPackager.Engine.dll
6.0.0.107

SQLCompare 8.0.0.965

pil0t

How to reproduce problem:

compare test1 and test2

CREATE DATABASE [test1]
GO
USE test1
GO

CREATE SCHEMA [testSchema]
GO

CREATE TABLE [testSchema].t1(i INT)
GO

CREATE TRIGGER [trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a INT;
END


CREATE DATABASE [test2]
GO
USE test2
GO

CREATE SCHEMA [testSchema]
GO

CREATE TABLE [testSchema].t1(i INT)
GO

CREATE TRIGGER [trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a2 INT;
END



if I use SQLCompare, i got script

ALTER TRIGGER [testSchema].[trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a INT;
END

if i use SDK, i got script

ALTER TRIGGER [trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a INT;
END

Anu D

Thanks for your detailed information in previous post, it helped us to recreate the issue.

Following things can be done:
1. If your project doesn't need any Data comparison you can use Compare engine 8 in your SDK project which will work similar to Compare 8 product.
2. If your project needs data comparison to be done you will have to wait for Data compare version 8 release which is sometime this month (We will publish in our Data compare forum about the release!)
3. If you are using Packager to build your project and cannot change the Comparison Engine you will have to wait for next packager release which is not yet scheduled.
4. If your project needs Schema compare and Data compare to be done you can create two different projects:
a. Schema Comparison Project : Using SDK with Compare 8 engine
b. Data Comparison Project: Using SDK with Data Compare engine.

Kindly let us know if that was helpful and if you have any queries regarding this,I'll like to help.