Repository navigation
DacFx generates unecessary "ALTER TRIGGER" when trigger schema is not defined #761
Description
Activity
From document, the schema is optional for CREATE TRIGGER, however required for REQUIRE TRIGGER
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;
GOThen 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
Dammshine "Then the second publish won't generate an extra ALTER TRIGGER command"
Is that not the desired behaviour?
The second is indeed the desired behaviour, thank you!
Tho it's still a bug because CREATE TRIGGER allows schema name to be optional.
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.';
GOthe 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.
Steps to Reproduce:
deploy-script.sqlgenerated fromsqlpackage /scriptattach belowDid this occur in prior versions? If not - which version(s) did it work in?
(DacFx/SqlPackage/SSMS/Azure Data Studio)