Category Archives: Back to Basics

The Trigger Fired?!

Data manipulation language (DML) triggers are commonly used to enforce business rules on underlying data, responding to a data change event by initiating a follow-on action, such as modifying additional data, generating a notification, or performing virtually any other action. While the basic trigger functionality is well known, there are a few interesting nuances regarding when a trigger fires. Let’s take a closer look.

To get started, let’s create a test table with a basic DML trigger and see how it works.

-- set the sql execution context
USE dev;
GO

-- suppress record count display
SET NOCOUNT ON;

-- if the test table exists, drop it
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

-- create the test table
CREATE TABLE dbo.tbl (
    ID INT IDENTITY NOT NULL
    , Col1 CHAR(1) NULL);
GO

-- create a trigger on the test table
CREATE TRIGGER dbo.trgTbl ON dbo.tbl
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    PRINT 'The trigger fired';
END
GO

Simple enough. With the above trigger we can now see whenever a DML statement hits the table. Let’s try the CRUD (CReate, Update, Delete) statements and confirm everything is working.

-- insert to the test table
INSERT INTO dbo.tbl (Col1) VALUES ('a'), ('b');

-- update the test table
UPDATE dbo.tbl SET Col1 = 'c' WHERE Col1 = 'a';

-- delete from the test table
DELETE FROM dbo.tbl;

With each of those statements we are returned confirmation that the trigger did, in fact, fire. Nothing interesting yet. But, what if we execute a non-update? And we can test the non-update in two types: a logical non-update and a physical non-update, which I’ll label types “a” and “b”, respectively.

-- update the test table with logical non-update (type a)
UPDATE dbo.tbl SET Col1 = Col1;

-- update the test table with physical non-update (type b)
UPDATE dbo.tbl SET Value = 'c' WHERE 0 = 1;

The result of the first statement execution is not particularly surprising. Though logically the SQL is not doing anything meaningful, that the trigger fires is intuitive as there are records affected. But what about the physical non-update (type b)? For the second statement, the WHERE clause ensures that there are no records in the result set. That fact might imply that the trigger should not fire because no records could be affected. However, as you will have seen from executing the statement, that is not the case. The trigger still fires even though nothing could have changed in the underlying data.

So, what’s the take away? Well, now that we’ve seen a trigger fire without underlying DML, we can take a simple step to prevent it (if appropriate based on the functional use-case). For example, the following will do the trick.

-- alter the trigger to ignore type b non-updates
ALTER TRIGGER dbo.trgTbl ON dbo.tbl
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    PRINT 'The trigger fired';
END
GO

By first checking if there are records to be updated, we can choose to exit the trigger immediately before incurring any additional overhead from potentially complex or unwanted SQL execution.

Finally, be sure to keep your workplace tidy!

-- clean up
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

For Each DB… Almost

The potential problems with the undocumented system procedure sp_MSforeachdb are well known to seasoned TSQL developers. Yes, it doesn’t pickup all databases (only those that are accessible at the time of execution). Yes, under certain versions of SQL Server (such as 2005 RTM) it picks up snapshots as well. Yes, it is a cursor behind the scenes and thus doesn’t offer any performance advantage to rolling your own.

And here is one more problem: it doesn’t trap @@FETCH_STATUS = -2.

What is the significance of that? Well, it is one more way for the procedure to let you down in a way you didn’t expect. Let’s take a closer look.

The following code will use the system procedure to list every* database on the server (*within the constraints of accessibility mentioned above! ;).

-- declare and set a sql string to print the database name
DECLARE @sql NVARCHAR(2000) = N'PRINT ''?'';';

-- use sp_MSforeachdb to print the name of "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

On my DEV server, the above query generates the following result:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB
devDB
devDW

Six “system” databases plus a dev database and a dev data warehouse. So far so good. But… what if one of those databases goes offline while the procedure is running? Let’s find out!

For this example I will take the second to last database (devDB) offline during procedure execution. If you are testing at home, choose any database except a system database or the last database in your list. The system database restriction should be obvious; excluding the last database will become apparent after the demo.

-- declare and set a sql string to take the "devDB" database offline
-- and print the context database name from the MSforeachdb procedure
DECLARE @sql NVARCHAR(2000) = N'IF ''?'' = ''master''
    ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
PRINT ''?'';';

-- use sp_MSforeachdb to execute the sql against "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

On my DEV system, when I execute the previous query the following results are returned:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB

Notice anything? Not only is devDB missing from the list… but so is devDW! And yet, no execution errors reported, so I’d have no idea anything went wrong.

But, what actually did go wrong? As I mentioned, the problem is that sp_MSforeachdb does not trap @@FETCH_STATUS = -2. To demonstrate, let’s first recreate what the procedure is doing.

-- declare the cursor output variable
DECLARE @sysDBName SYSNAME;

-- declare and initialize the cursor
DECLARE crs CURSOR FOR SELECT name FROM sys.databases;

-- open the cursor
OPEN crs;

-- initialize the cursor output
FETCH NEXT FROM crs INTO @sysDBName;

-- iterate through the cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
    -- take the "devDB" offline
    IF @sysDBName = 'master'
        ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
    -- print the cursor context database
    PRINT @sysDBName;
    -- fetch the next value from the cursor
    FETCH NEXT FROM crs INTO @sysDBName;
END

-- close cursor
CLOSE crs;

-- release cursor from memory
DEALLOCATE crs;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

The result of the above query is the same as the procedure; devDW is missing and no error is returned. But now we can easily add some additional syntax to identify and report on the issue. For example, add the following immediately after the FETCH command within the WHILE loop:

-- trap for missing records
WHILE @@FETCH_STATUS = -2 BEGIN
    PRINT 'The row fetched is missing. Continuing to the next row....';
    FETCH NEXT FROM crs INTO @sysDBName;
END

Now we can see that the devDB row could not be fetched because it is missing from the cursor record set. But we can also chose to continue on to the next record, or take other action as needed. Another approach could be to loop through cursor results on @@FETCH_STATUS <> -1 instead of = 0. This example also shows why we didn’t get an error record — because, despite the unexpected results, no error actually occurred! @@FETCH_STATUS -2 is not an error, but a possibility that SQL Server anticipates and has already enumerated. See here for more info.

While we don’t need any more reasons to abandon sp_MSforeachdb, I suppose it is worth remembering that the procedure is undocumented for a reason.

Break!

What is the difference between a breaking space and a non-breaking space? About five minutes of frustration.

Let’s take a look at the potential confusion. First, let’s create a temporary table and insert two records, one with a breaking space, and another with a non-breaking space. We’ll use the ASCII codes to ensure we get it right (and because it will allow you to copy from this post and try it yourself).

-- declare a temporary table
DECLARE @tbl TABLE (
    Value VARCHAR(50)
    );

-- insert two records to the table
INSERT INTO @tbl
VALUES
    ('Hello' + CHAR(32) + 'World')
    ,('Hello' + CHAR(160) + 'World')
    ;

-- select the results
SELECT DISTINCT Value FROM @tbl;

Notice anything funny about the result set? Yup—in the normal grid view, the records look identical, and yet they are not collapsed by the DISTINCT statement. Unless you have non-breaking spaces on the mind, that may be an unexpected result.

The good news is there’s an easy way to check for non-breaking spaces. Simply copy the result grid and paste it to the query editor. You’ll notice that the non-breaking space is visually identified as a solid red line. Thus it’s a rather easy difference to check for.

And one of the best things about this example? No clean-up needed! 🙂

Transactions Really Do Work

Believe it or not, transactions really do work — even when you forget to think about them! In the example below we’ll walk through how transaction rollback can prevent an attempt at error capture.

For this example, we will first create a table in which to store any errors we generate.

-- Create a table in which to store error messages
IF OBJECT_ID(N'tblLog') IS NOT NULL
    DROP TABLE tblLog;
CREATE TABLE tblLog (
    ID INT IDENTITY(1,1) NOT NULL
    , DateStamp DATETIME2(3) NOT NULL
    , ErrorNumber INT NOT NULL
    , ErrorMessage NVARCHAR(2048) NOT NULL
    )
;
ALTER TABLE tblLog
ADD CONSTRAINT DF_tblLog_DateStamp
DEFAULT (GETDATE()) FOR DateStamp
;
GO

With our error capture table created, let us now generate some errors! Specifically, we will produce an uncommitable transaction error so that we can eventually demonstrate how transaction rollback can affect error capture.

-- First, let's demonstrate capturing an uncommitable
-- transaction error
BEGIN TRY
    -- This is an uncommitable transaction because the
    -- table does not exist in the database
    DROP TABLE tblDoesNotExist;
END TRY
BEGIN CATCH
    -- Here is the catch where we capture the error
    INSERT INTO tblLog (ErrorNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
END CATCH

Assuming tblDoesNotExist does not, in fact, exist in your database, the above statement will result in an entry to the error capture table, tblLog. Let’s take a look at the error:

-- Notice that we've captured the error
SELECT * FROM tblLog;

Yes, indeed, the error was captured; and it told us that the table does not exist and we thus cannot delete it. So far, so good. This information is useful for us to be able to troubleshoot the problem.  Let’s clear the error log now to reset it for our next example.

-- Clear the log table
DELETE FROM tblLog;

In the following example, we will take the exact statement we ran above and put it inside a transaction that includes rollback logic. Using rollback logic of this type is very common and is good practice. In fact, more robust validation and tracking can be implemented — but that is superfluous to the current demonstration.

-- Now, let's see how error capture can be lost in a transaction
BEGIN TRY
    BEGIN TRANSACTION;
        -- Here is the code from above, but run
        -- within a transaction with rollback logic
        BEGIN TRY
            -- This is an uncommitable transaction because the
            -- table does not exist in the database
            DROP TABLE tblDoesNotExist;
        END TRY
        BEGIN CATCH
            -- Here is the inner catch where we attempt to
            -- capture the error
            INSERT INTO tblLog (ErrorNumber, ErrorMessage)
            VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
        END CATCH
    -- This commit will fail because the transaction is in
    -- an uncommitable state due to the attempted DROP TABLE
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- The catch is called; transaction must be rolled back
    ROLLBACK TRANSACTION;
    -- Capture the outer error
    INSERT INTO tblLog (ErrorNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
END CATCH

At first glance, it might seem like we would only expect the same, one error in our log table. Afterall, the DROP TABLE command was in a TRY-CATCH block and so it was “handled”, right? And, if it was handled, then the outer CATCH block would not even execute. Right? Not exactly.

The DROP TABLE command will fail and will result in the error being written to the log table. However, because that error makes the transaction uncommitable, the COMMIT TRANSACTION also fails and pushes execution to the outer CATCH block. And, in the outer CATCH block, the transaction is rolled back. Which means writing the inner error to the log does NOT occur! Let’s see what the log shows:

-- Notice that only the outer error is captured; the
-- inner error capture is rolled back in the outer catch
SELECT * FROM tblLog;

While I hope this example is easy to follow (as examples should be), I trust that you can imagine how the situation will get confusing if, rather than a single DROP TABLE command, there were nested procedures with their own transactions and try-catch blocks. At any point in that potentially complex web of execution a statement might fail. And, despite our intention to capture the error at the point of failure, all of that error capture activity could get rolled-back and the end result might be a simple statement saying the transaction was uncommitable. A single error statement such as that provides virtually no insight into which part of the transaction was uncommitable — only that something within it was uncommitable.

What can be done about that? Pay close attention to where your transactions begin and end, and ensure you are not capturing errors where they will not be persisted!

Oh yes, and don’t forget to clean up after yourself! 🙂

-- Clean up
IF OBJECT_ID(N'tblLog') IS NOT NULL
    DROP TABLE tblLog;

GOs and Semicolons

In every programming language there are phrases or elements that appear frequently and yet seem innocuous. Two such elements in T-SQL are the GO command and the semicolon.

The GO command tells the SQL server to execute/commit any preceding T-SQL statements. When there is only one statement in a batch, the GO command has no distinct effect. But, in multi-statement batches, the placement of GO could not only be important, it could, in fact, be required. Let’s take a look by walking through an example.

The following three statements, when executed as a batch (all at once), will fail with error message 111, stating that “CREATE VIEW” must be the first statement in a batch. In our example, it is not; it is the second statement, right after we specify the database context.

/* This fails because CREATE VIEW must be the first statement in a batch transaction */
USE [master];
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
DROP VIEW vwTest;

To address this error, let’s put a GO after we specify the database context. While that resolves error message 111, running the statements again as a batch will result in error message 156, stating incorrect syntax near the keyword “DROP”.

/* This fails because CREATE VIEW must be committed before continuing in a batch transaction */
USE [master];
GO
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
DROP VIEW vwTest;

And so, to resolve the second error, let’s put another GO after the CREATE VIEW statement to ensure it is committed before continuing on to the DROP statement. The following statements executed as a batch will succeed.

/* This completes successfully */
USE [master];
GO
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
GO
DROP VIEW vwTest;

As the preceding examples demonstrate, the GO command is useful in batch operations to specify points in the batch where prior statements should be completed before continuing to later statements. There are other use-cases, mostly related to ensuring that newly-created objects are in place before subsequently referencing them the batch.

Let’s now shift to a more subtle issue: that of the semicolon. In T-SQL, a semicolon is designated as the statement terminator (similar to many other programming languages). In all SQL Server versions through 2012 (Denali), terminating a statement with a semicolon is optional in almost every instance. However, be aware that it will not be optional in a future release (or, at least so-says Microsoft at this point). Take a look at this TechNet article and see for yourself. On the other hand, the lack of semicolons in the wild is so pervasive that it will be difficult to enforce their usage anytime soon.

Aside from the potential that terminating T-SQL statements with a semicolon might be required in the nearish future, when else might it be needed? There are a few, specific instances, such as in the definition of a common table expression (CTE). A CTE is a T-SQL statement that resolves to a record set which, once defined, can be referenced multiple times, but only within the same scope. Additional information on CTEs is available here, and perhaps will be the subject of a future post within this forum.

Regardless, CTEs require that preceding T-SQL statements are terminated with a semicolon. Let’s take a look.

The following batch will fail with error message 319, stating incorrect syntax near the keyword “with”. The full message is actually uncharacteristically helpful and suggests that it might be caused by a missing semicolon.

/* This fails because common table expression definitions require prior statements to be semicolon terminated */
PRINT 'Start'
WITH cte AS (SELECT 'Col1' = 1)
SELECT * FROM cte
;

Now that we’ve seen the error message and confirmed the batch does not execute, let’s add the semicolon and see what happens.

/* This completes sucessfully */
PRINT 'Start'; -- add a semicolon here
WITH cte AS (SELECT 'Col1' = 1)
SELECT * FROM cte
;

As you will see, the previous batch executes successfully. While it’s not a good example of using a CTE, it does demonstrate that the semicolon was necessary for the CTE construct. At this point, let’s shift perspective and look at the use of semicolons a little bit differently: what if we provide too many semicolons? Let’s add one to the batch after defining the CTE and see what happens.

/* This fails because common table expressions are only valid within a statement (and a semicolon terminates a statement) */
PRINT 'Start';
WITH cte AS (SELECT 'Col1' = 1); -- add a semicolon here
SELECT * FROM cte
;

In this case, the batch fails with error message 1o2, incorrect syntax near “;”. That error is due to SQL not being able to resolve the “FROM cte” part of the statement. As mentioned previously, a CTE is only valid within the T-SQL statement in which it was defined. Since the semicolon ended the CTE definition statement, the following FROM reference to the CTE is invalid.

So, bottom line regarding semicolons: 1) get in the habit of using them, because they are eventually going to be required, and 2) by using them frequently you’ll develop a natural sense of where statements begin and end, and thus be better able to plan and manage transactions, batches, and, in general, good coding practice.