The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,876 other followers

SQL-Server: Triggers fire on multiple records – what to watch for

Posted by jpluimers on 2009/09/16

SQL Server triggers can operate on multiple records at once.
So it is important to not only to make your triggers work properly, but also make them work performantly.

Given a table like the Users table below, which in the future is likely to be extended with fields like Initials, MaidenName, Suffix, Prefix, etc.
The client has choosen not to make the FullName calculated, but update it using a trigger.

CREATE TABLE [dbo].[Users](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[FullName] [nvarchar](100) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](100) NULL
) ON [PRIMARY]

A trigger that updates the FullName field is only needed for Insert and Update statements.
It needs to update the FullName for all the records in that statement, but not for any other records.

Quite a lot of people consider using cursors in triggers because of potential performance degradation (like explained here, here and here), but there are tricks to improve performance.

Personally, I am a bit ambivalent about cursors: they can assist in solving complex logic, introduce quite some code bloat, and require you to type your intermediate variables correctly, and maintain those types when the underlying table definitions change.
Furthermore, cursors have names and with any of those: you should be careful naming them in a meaningful manner, without duplicating names.
So when you do use cursors: be prepared for some potentially incurred development and maintenance costs.

So here is an example trigger that updates the FullName column:

ALTER TRIGGER [dbo].[UpdateUsersFullName]
   ON  [dbo].[Users]
   AFTER INSERT,UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    update Users
    set FullName =
      (
        select rtrim(ltrim(coalesce(i.FirstName, '') + ' ' + coalesce(i.LastName, ''))) 
        from Inserted i
        where i.UserId = u.UserId -- single row from Inserted table
      )
    from Users u
    where u.UserId in -- only rows in Inserted table
      (
        select i.UserId
        from Inserted i
      );
END

This example does not use a cursor, but a nested statement: the select is nested in the update.
Two performance optimizations are done:

  1. The Users u is only updated for rows that exist in the (local to the trigger) Inserted i table.
  2. The Inserted i table is only queried for the current UserId from the Users u table.

The second is also needed to prevent the dreaded error message like this when the trigger gets called when processing a multi-record update, multi-record insert or insert/select combination:

Msg 512, Level 16, State 1, Procedure UpdateUsersFullName, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

Note that this could have been solved by using “Instead Of” triggers as well.
But those require you to do all of the actions a normal insert or update would do, with similar effects as sketched above when the underlying table changes…

–jeroen

2 Responses to “SQL-Server: Triggers fire on multiple records – what to watch for”

  1. MKU said

    This will do the same thing without any subqueries.
    ——————————–
    update Users
    set FullName = rtrim(ltrim(coalesce(i.FirstName, ”) + ‘ ‘ + coalesce(i.LastName, ”)))
    from Inserted i, Users u
    where i.UserId = u.UserId
    ——————————–

    • jpluimers said

      You are so right.

      Being bitten too often by getting the carthesian product when using classic (non joined) queries and not having the where clause exactly right, I didn’t even think of going the classic way here.

      Good point, thanks!

      For queries that cannot be solved the ‘classic’ way, the tips of the original query still hold:
      – limit the number of records actually being updated to the list of records passed in Inserted
      – limit the number of Inserted references to the ones actually being updated

      Thanks again,

      –jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: