Sonar Rule tsql:S1116 not correct for ENABLE, DISABLE Trigger Statements

I think the rule tsql:S1116 Empty statements should be removed is not always correct for T-SQL using ENABLE/DISABLE TRIGGER Statements.
In some cases we have to ENABLE/DISABLE Triggers in IF Blocks

IF (@disable=1)
BEGIN
	;DISABLE TRIGGER [dbo].[Trigger] ON [dbo].[Table];
END

or

IF (@enable=1)
BEGIN;
	ENABLE TRIGGER [dbo].[Trigger] ON [dbo].[Table];
END

For correct SQL Syntax there must be a semicolon before the statement.

THX

Hey there.

What version of SonarQube are you using?

Hi!

  • Enterprise Edition
  • Version 10.2.1 (build 78527)

THX

Hello @ArminPrieschl,

From my understanding, BEGIN is not a statement as it is part of the BEGIN...END statement.
So, in my opinion, S1116 is correctly raised here.

I don’t understand precisely what you mean by that. Do you mean that each statement should have a semicolon for valid SQL at its beginning?
Documentation says that the semicolon is a statement terminator, I would expect that the semicolon needs to be after, at the end of the statement.

Transact-SQL statement terminator. Although the semicolon isn’t required for most statements in this version of SQL Server, it will be required in a future version.

Based on this, here is what you could have for your example:

IF (@disable=1)
BEGIN
	DISABLE TRIGGER [dbo].[Trigger] ON [dbo].[Table];
END;

I hope this clears everything.
Have a nice day.

Hi @sebastien.marichal

You’r right the Statement is executing correctly.

But we use SQL Sever Database Projects in Visual Studio.

Without the Semicolon before we can’t build it.

And in Microsoft SQL Server Management Studio the Error is also shown.

Maybe the Bug is located there.
I’ll start a request @ Microsoft.

THX

Hello @ArminPrieschl,

Let us know if your request to Microsoft brings more light on this.

Thank you,

Microsoft Request
Developer Community?

1 Like