Sql – Copy Trigger slows database operations down considerably

sqlsql-server-2005tsql

Our original database has called first, middle, last for names of people. We have now created fields called firstname, middlename, and lastname. While we are going through this transition I have created a trigger to copy data from first to firstname, middle to middlename, and last to lastname anytime one the previous fields receive an update or insert. The issue is when we batch upload files into our database it slows everything way down and may cause other events to timeout.

Below is my code:

USE [Database]
GO
/****** Object:  Trigger [dbo].[CopyFirstName]    Script Date: 07/15/2009 08:35:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[CopyFirstName]

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

     IF UPDATE([First]) BEGIN
        SET NOCOUNT ON;
declare @ChangedField nvarchar(50);

  select @ChangedField = [First] from inserted 
  update dbo.Patients set FirstName  = @ChangedField where [First]  = @ChangedField


END
end

Could this be improved or done another way?

Best Answer

Some thoughts:

  • If you have one trigger each for first, middle and last, then I guess you have 3 triggers, 3 scans of the "inserted" table, and 3 updates

  • Your current code does not support multiple row updates or inserts

  • By using "...where [First] = @ChangedField" then if you'll probably be updating multiple rows. Let's say you entered "John" or "Hans". Although John or Hans is correct, you're still updating every row with John or Hans. And ignoring other rows inserted (see previous point)

Options:

  • Combine into one trigger
  • Update only changed rows
  • Add a filter for old/new column differences
  • Reduce work (eg combine one statement for all columns)

Example of what you could do:

ALTER TRIGGER [dbo].[CopyNames]
   ON  [dbo].[Patients]
   AFTER  INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM INSERTED)
    RETURN;

IF UPDATE(First) OR UPDATE(Middle) OR UPDATE (Last)
  update
      P
  set
      FirstName  = INS.First,
      Middlename  = INS.Middle,
      LastName  = INS.Last
  FROM
      dbo.Patients P
      JOIN
      INSERTED INS ON P.KeyColumn = INS.KeyColumn
/* optional, try it and see
  WHERE
      P.FirstName <> INS.First OR
      P.Middlename <> INS.Middle OR
      P.LastName <> INS.Last
*/


END
Related Topic