What are the challenges you face when working across database platforms? Take the survey
Options

ALTER TRIGGER without schema

pil0tpil0t Posts: 14
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?

Comments

  • Options
    Anu DAnu D Posts: 876 Silver 3
    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?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    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
  • Options
    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
  • Options
    Anu DAnu D Posts: 876 Silver 3
    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.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
Sign In or Register to comment.