Source control a trigger on a table but not the table structure

Bugmeister · July 27, 2020 3:50AM

Hi,

I thought I'd asked this before but perhaps not. On a 3rd party database we'd like to source control out enhancements/modifications but not the vendor-supplied objects. Therefore we filter out vendor tables/objects, however we do have instances where we have a custom trigger on a table that (apart from the trigger) we want to ignore.

If there any way to source-control that trigger but not the base table? I suspect not but thought I'd ask in case there was some alternative thought I hadn't considered.

Thanks
Peter

AlexYates · July 27, 2020 10:50AM

I would use a post-deploy script for this that does a:

CREATE OR ALTER TRIGGER <TRIGGER_NAME>
ON TABLE <TABLE_NAME>
etc

(Note, OR ALTER is only supported from SQL 2016 up.)

If I have a lot of these I would extract them into a helper script or sproc so that the post-deploy script can simply reference the helper script/sproc to update all the triggers.

Bugmeister · July 27, 2020 11:42PM

OK that's an idea Alex. Only downside is we'd have to remember (or I guess it could be automated) to include any changes (i.e. it wouldn't be caught by SSC's "Commit" tab if a change was made to the trigger in-DB).

AlexYates · July 28, 2020 9:21AM

That's correct.

You would need to ensure everyone understands that changes to triggers on filtered out tables need to use the post deploy script instead.

If you extract the trigger creation into a sproc, and then call the sproc from the poat-deploy, changes will be spotted by SoC, but you would still need to remember to update the poat-deploy.

Alternatively, you could have a DEPLOY_ALL_TRIGGERS sproc that loops through all the DEPLOY_TRIGGER_1, DEPLOY_TRIGGER_2, etc scripts based on some naming convention.

That way all you need to put in your poat-deploy is:

EXEC DEPLOY_ALL_TRIGGERS

And you shouldn't ever need to update it again. Of course, folks will still need to remember that the triggers are handled with the associated sprocs.

An alternative approach would be to add some tests to your build/deploy pipelines to ensure that all the appropriately named deploy_trigger sprocs are referenced by the coordinating script and vice versa.

AlexYates · July 28, 2020 9:22AM

Wow, not sure why post-deploy auto corrected to poat-deploy?

Pete_Ruiz · July 28, 2020 4:15PM

Hello @Bugmeister,

Thanks for your query about SQL Source Control. If the trigger definition is on the table definition, unfortunately, there wouldn't be a way include the trigger if you've filtered out the table.

Alex's suggestion is probably the best idea for handling this.

I hope this is helpful. Please let me know if you have any questions.

Kind regards,

Pete Ruiz

Source control a trigger on a table but not the table structure

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk