2

I have asked this question on MSDN forum without any response. Maybe here someone could help me?

I think it's a bug in SSDT and quite old one. I've tried several versions of SSDT binaries but the problem persists. SQLPackage.exe doesn't exclude trigger properly especially if it is encrypted. The situation:

  1. Create simple table dbo.TheTable and encrypted trigger in empty database: 
create table dbo.TheTable(Name varchar(30) not null);
create trigger dbo.trgInsTheTable on dbo.TheTable with encryption for insert as print 'insert to TheTable';
  1. Use SQLPackage.exe and extract (Action:Extract) that database to dacpac: 
SqlPackage.exe /Action:Extract /TargetFile:"DB.dacpac" /SourceDatabaseName:"DB" /SourceServerName:".\Sandbox"
  1. Run SQLPackage.exe to generate scripts (Action:Script), the same database can be used:
SqlPackage.exe /Action:Script /SourceFile:".\DB.dacpac" /Profile:".\Sandbox.Publish.xml" /p:CreateNewDatabase=True /OutputPath:".\DB.sql"

And following error is thrown:

Error SQL0: The element [dbo].[trgInsTheTable] cannot be deployed as the script body is encrypted.

  1. OK, now let's try to have just table TheTable generated and for that use /p:ExludeObjectTypes=DatabaseTriggers;ServerTriggers:

SqlPackage.exe /Action:Script /SourceFile:".\DB.dacpac" /Profile:".\Sandbox.Publish.xml" /p:CreateNewDatabase=True /OutputPath:".\DB.sql" /p:ExcludeObjectTypes=DatabaseTriggers;ServerTriggers

The above error is thrown again. The only but not acceptable way is to add Tables to ExludeObjectTypes. Then this trigger is excluded but together with table it is created on.

The publish XML file can be anything default.

What am I missing here?

Brun0
  • 21
  • 2
  • Database triggers and Server triggers are both types of [DDL Triggers](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers). Table triggers are neither Database nor Server triggers, so one would not expect that excluding either of those types would prevent table triggers from being included. – Damien_The_Unbeliever Nov 24 '17 at 08:10
  • Thanks @Damien_The_Unbeliever for comment - it explains the situation. I've tried excluding simple not encrypted trigger and it didn't work as well. That means it's a feature not to be able to specifically exclude any DML triggers. This also means that it's impossible yet to have dacpac created from database with all code encrypted and deploy only specific objects from it excluding all encrypted code. – Brun0 Nov 24 '17 at 08:54

3 Answers3

0

It seems that it's not a bug but feature not to be able to exclude any DML triggers. I tried excluding simple not encrypted trigger and it also didn't work.

Now I need to think how to overcome the situation when I have database dacpac where all code is encrypted, I want just tables from that dacpac deployed and I can't exclude encrypted DML trigger while all other code like stored procedures and functions have been successfully excluded.

Hope this will be fixed in one of the next SSDT releases.

Brun0
  • 21
  • 2
0

I found a similar problem. I am trying to update a trigger with new content, but the trigger should be left disabled when he's finished. The trigger IS disabled before he starts. The trigger is enabled when he's finished. WTH?

The dac publish operation updates the trigger content, but enables the trigger along with it. I ran the test case dozens of times now. I just restore the db and run again. Same issue.

I run he dac operation a second time on the modified table and the dac notices the enabled trigger and disables it. No other operations to do. So the work around is to run all your dac operations twice to clean up any triggers that accidentally get enabled on the first pass.

I do believe this is a bug of some sort. A particularly nasty one when you have triggers getting enabled without permission.

user5855178
  • 567
  • 1
  • 7
  • 17
  • I just did some investigation and it appears this is not a bug, but expected behavior of sql server. Any alterations of disabled triggers will set the trigger to enabled when you are done. I guess Microsoft thinks if you change the content of a trigger that they're helping you out by enabling it without any notice. – user5855178 Feb 18 '18 at 13:37
0

It looks like DML Triggers can't be excluded? :(

Tried this recent Release of sqlpackage.exe October 29, 2019 18.4 15.0.4573.2

Mahesh
  • 117
  • 1
  • 6