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,831 other followers

Archive for September 16th, 2009

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.
Read the rest of this entry »

Posted in Database Development, Development, SQL Server | 2 Comments »

 
%d bloggers like this: