What’s next in ApexSQL Complete 2017 R6 – Execution alerts

This feature will be able to analyze a query that is about to be executed against defined set of rules with the ability to block query execution and produce a warning message.

For example, if DELETE statement without WHERE clause is executed like the statement below:

DELETE * FROM Person.Address

the following warning message will appear:

The Execution alerts feature will initially come with three built-in alerts:

1. UPDATE statement without a WHERE clause
   Triggers an alert on a query execution that contains an UPDATE statement without WHERE clause
2. DELETE statement without WHERE clause
   Triggers an alert on a query execution that contains a DELETE statement without a WHERE clause
3. TRUNCATE statement
   Triggers an alert on a query execution that contains a TRUNCATE statement

Under the Executed alerts tab in the list of the item, a user can see all alerts that are set. Also, the user can set which rules will be included in the executing process of the queries by checking the check boxes under the Status column. Additionally, the items in the execution alerts list can be deleted, edited or a new rule for the execution alerts can be created:

On the left side of the New alert window, the Servers/Databases can be chosen which the new rule can be applied to and on the right side of the New alert window, the name of the rule and conditions can be set.

Rules are divided into 4 different components:

1. Query connection
2. Statement context
3. Statement clause
4. Custom text

Query connection

This component of an alert will specify the database scope that the alert is applied to. The scope can be all databases in all servers, or a single, individual database

Statement context

The statement context option defines the alert scope based on the type of statement e.g. CREATE, UPDATE, DELETE, TRUNCATE.

For example, if an alert is created on the Select statement like on the image below:

The execution alert message will appear on the AW2014 and TestDatabase for every executed query that contains a SELECT statement:

Statement clause

The statement clause option offers checking for existence or lack specific clause in SQL statement that is being analyzed. For example, it allows checking if Delete statement doesn`t contain Where clause or A CREATE TABLE statement contains ON [PRIMARY]:

Now, when the following code is executed:

CREATE TABLE [Person].[AddressType] (
	[AddressTypeID] [int] IDENTITY(1, 1) NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED ([AddressTypeID] ASC) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]

The following message will appear:

Custom text

The custom text rule component is used for checking if a statement contains or doesn’t contain a defined text string.

For example, if the execution of a query contains an INNER JOIN clause is to be prevented, choose the Custom text item in the Condition drop down box, then select the Contains item in Condition, and finally, in the Value field type, specify INNER JOIN:

Now, when the code that contains the INNER JOIN clause is executed:

SELECT a.*
FROM Person.Address a
INNER JOIN Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID

The following message will appear: