DacFx generates unecessary "ALTER TRIGGER" when trigger schema is not defined

[Dammshine]

• SqlPackage or DacFx Version: 170.1.61.1
• .NET Framework (Windows-only) or .NET Core: 8.0.411
• Environment (local platform and source/target platforms): MacOS, to mssql docker runs in container 2022

Steps to Reproduce:

1. Initialise empty sqlproj,
2. Create a customise schema, and create trigger

CREATE SCHEMA custom
GO;

CREATE TABLE [custom].[Table1]
(
    [Id] INT NOT NULL PRIMARY KEY
);
GO;

CREATE TRIGGER Table1Trigger ON [custom].[Table1]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
END;
GO

3. Reproduced, the second publish failed,

david.zhou@DZhou1 TriggerBugRepro % sqlpackage /Action:Publish \
  /SourceFile:./bin/Debug/TriggerBugRepro.dacpac \
  /TargetConnectionString:"Data Source=mssql-dma.docker.ttddev.adsrvr.org;Initial Catalog=TriggerBugRepro;User Id=appuser;Password=abc123;Pooling=False;TrustServerCertificate=true"
Publishing to database 'TriggerBugRepro' on server 'mssql-dma.docker.ttddev.adsrvr.org'.
Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Creating Schema [custom]...
Creating Table [custom].[Table1]...
Creating Trigger [custom].[Table1Trigger]...
Update complete.
Updating database (Complete)
Successfully published database.
Time elapsed 0:00:11.17
david.zhou@DZhou1 TriggerBugRepro % sqlpackage /Action:Publish \
  /SourceFile:./bin/Debug/TriggerBugRepro.dacpac \
  /TargetConnectionString:"Data Source=mssql-dma.docker.ttddev.adsrvr.org;Initial Catalog=TriggerBugRepro;User Id=appuser;Password=abc123;Pooling=False;TrustServerCertificate=true"
Publishing to database 'TriggerBugRepro' on server 'mssql-dma.docker.ttddev.adsrvr.org'.
Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Altering Trigger [custom].[Table1Trigger]...
An error occurred while the batch was being executed.
Updating database (Failed)
*** Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 208, Level 16, State 6, Procedure Table1Trigger, Line 2 Invalid object name 'Table1Trigger'.
Error SQL72045: Script execution error.  The executed script:
ALTER TRIGGER Table1Trigger
    ON [custom].[Table1]
    AFTER INSERT, UPDATE, DELETE
    AS BEGIN
           SET NOCOUNT ON;
       END



Time elapsed 0:00:06.76

4. The deploy-script.sql generated from sqlpackage /script attach below

/*
Deployment script for TriggerBugRepro

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "TriggerBugRepro"
:setvar DefaultFilePrefix "TriggerBugRepro"
:setvar DefaultDataPath "/var/opt/mssql/data/"
:setvar DefaultLogPath "/var/opt/mssql/data/"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'Altering Trigger [custom].[Table1Trigger]...';


GO

ALTER TRIGGER Table1Trigger ON [custom].[Table1]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
END;
GO
PRINT N'Update complete.';


GO

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[Dammshine]

From document, the schema is optional for CREATE TRIGGER, however required for REQUIRE TRIGGER

[ErikEJ]

@Dammshine What happens if you change your script?

CREATE TRIGGER [custom].[Table1Trigger] ON [custom].[Table1]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
END;
GO

[Dammshine]

Then SqlPackage is able to detect there's no change. Tho it's still a bug requires attention as CREATE TRIGGER allows schema name to be optional.
If I change to above, then for the firstsqlpackage publish, it will publish below script.

/*
Deployment script for TriggerBugRepro

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "TriggerBugRepro"
:setvar DefaultFilePrefix "TriggerBugRepro"
:setvar DefaultDataPath "/var/opt/mssql/data/"
:setvar DefaultLogPath "/var/opt/mssql/data/"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET PAGE_VERIFY NONE,
                DISABLE_BROKER 
            WITH ROLLBACK IMMEDIATE;
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET TARGET_RECOVERY_TIME = 0 SECONDS 
    WITH ROLLBACK IMMEDIATE;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), MAX_STORAGE_SIZE_MB = 100) 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE = OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
PRINT N'Creating Schema [custom]...';


GO
CREATE SCHEMA [custom]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating Table [custom].[Table1]...';


GO
CREATE TABLE [custom].[Table1] (
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Trigger [custom].[Table1Trigger]...';


GO

CREATE TRIGGER [custom].[Table1Trigger] ON [custom].[Table1]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
END;
GO
PRINT N'Update complete.';


GO

Then the second publish won't generate an extra ALTER TRIGGER command

/*
Deployment script for TriggerBugRepro

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "TriggerBugRepro"
:setvar DefaultFilePrefix "TriggerBugRepro"
:setvar DefaultDataPath "/var/opt/mssql/data/"
:setvar DefaultLogPath "/var/opt/mssql/data/"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'Update complete.';


GO

[ErikEJ]

@Dammshine "Then the second publish won't generate an extra ALTER TRIGGER command"

Is that not the desired behaviour?

[Dammshine]

The second is indeed the desired behaviour, thank you!
Tho it's still a bug because CREATE TRIGGER allows schema name to be optional.

[dzsquared]

bug recap: T-SQL enables table triggers to be defined without a schema, where they take the schema of their table.

this correctly builds in a project, but dacfx publish/deploy recognizes a change in the trigger on every deploy

...
GO
PRINT N'Altering Trigger [custom].[Table1Trigger]...';


GO

ALTER TRIGGER Table1Trigger ON [custom].[Table1]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
END;
GO
PRINT N'Update complete.';


GO

the comment in the deploy script includes the correct schema on the trigger ([custom].[Table1Trigger]) but the repeated alter suggests something is mismatching in the definition.

setting the schema on the trigger explicitly results in the deployment correctly not touching the trigger.