I need to optimize my first T-SQL update trigger

Question

How do I rewrite this update trigger without using a lot of variables?

I wrote my first SQL Server trigger and it works fine, but I think, that there must be an easier solution.

If minimum one of 5 columns is changed I write two new rows in another table. row 1 = old Fahrer (=Driver) and old dispodate and update-time row 2 = new Fahrer and new dispodate and updatedatetime My solution is just a copy of the foxpro-trigger, but there must be a easier solutions in T-SQL to check whether one colum is changed.

ALTER TRIGGER [dbo].[MyTrigger]
ON [dbo].[tbldisposaetze]
AFTER UPDATE
AS
SET NOCOUNT ON;
/*SET XACT_ABORT ON 
SET ARITHABORT ON 
*/
DECLARE @oldfahrer varchar(10)
DECLARE @oldbus varchar(10)
DECLARE @olddispodat date
DECLARE @oldvzeit decimal(4,0)
DECLARE @oldbzeit  decimal(4,0)
DECLARE @oldbeschreibk varchar(255)

DECLARE @newfahrer varchar(10)
DECLARE @newbus varchar(10)
DECLARE @newdispodat date
DECLARE @newvzeit decimal(4,0)
DECLARE @newbzeit  decimal(4,0)
DECLARE @newbeschreibk varchar(255)

    SELECT  @oldfahrer = fahrer,@oldbeschreibk=beschreibk,@oldbus=bus,@oldbzeit=bzeit,@olddispodat=dispodat,@oldvzeit=vzeit
        FROM DELETED D
    SELECT  @newfahrer = fahrer,@newbeschreibk=beschreibk,@newbus=bus,@newbzeit=bzeit,@newdispodat=dispodat,@newvzeit=vzeit
        FROM inserted I

if @oldbeschreibk <> @newbeschreibk or @oldbus <> @newbus or @oldbzeit <> @newbzeit or @oldfahrer <> @newfahrer or @oldvzeit <> @newvzeit 
begin
  IF (SELECT COUNT(*) FROM tbldispofahrer where fahrer=@oldfahrer and dispodat=@olddispodat) > 0 
    update tbldispofahrer  set laenderung = GETDATE()  where fahrer=@oldfahrer and dispodat=@olddispodat
  else
    INSERT into tbldispofahrer (fahrer,dispodat,laenderung) VALUES (@oldfahrer,@olddispodat,getdate())

  IF (SELECT COUNT(*) FROM tbldispofahrer where fahrer=@newfahrer and dispodat=@newdispodat) > 0 
    update tbldispofahrer  set laenderung = GETDATE()  where fahrer=@newfahrer and dispodat=@newdispodat
  else
    INSERT into tbldispofahrer (fahrer,dispodat,laenderung) VALUES (@newfahrer,@newdispodat,getdate())
 end 

Answer 1

There were a few little syntax errors in the answer from Daniel. The following code is running fine:

MERGE tbldispofahrer AS tgt
USING (
    SELECT d.fahrer, d.dispodat, GETDATE() [laenderung] from deleted d
    INNER JOIN inserted i ON i.satznr = d.satznr
        AND (i.fahrer <> d.fahrer OR i.beschreibk <> d.beschreibk or i.bus <> d.bus or i.bzeit <> d.bzeit or i.vzeit <> d.vzeit)
    UNION
    SELECT i.fahrer, i.dispodat, GETDATE() [laenderung] from inserted i
    INNER JOIN deleted d ON i.satznr = d.satznr
        AND  (i.fahrer <> d.fahrer OR i.beschreibk <> d.beschreibk or i.bus <> d.bus or i.bzeit <> d.bzeit or i.vzeit <> d.vzeit)
) AS src (fahrer, dispodat, laenderung)
ON tgt.fahrer = src.fahrer AND tgt.dispodat = src.dispodat
WHEN MATCHED THEN UPDATE SET
    laenderung = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (fahrer,dispodat,laenderung)
    VALUES (src.fahrer, src.dispodat, src.laenderung);

Answer 2

I'll assume you have SQL Server 2008 or greater. You can do this all in one statement without any variables.

Instead of doing all the work to first get the variables and see if they don't match, you can easily do that in as part of where clause. As folks have said in the comments, you can have multiple rows as part of inserted and deleted. In order to make sure you're working with the same updated row, you need to match by the primary key.

In order to insert or update the row, I'm using a MERGE statement. The source of the merge is a union with the where clause above, the top table in the union has the older fahrer, and the bottom has the new farher. Just like your inner IFs, existing rows are matched on farher and dispodat, and inserted or updated appropriately.

One thing I noticed, is that in your example newfahrer and oldfahrer could be exactly the same, so that only one insert or update should occur (i.e. if only bzeit was different). The union should prevent duplicate data from trying to get inserted. I do believe merge will error if there was.

MERGE tbldispofahrer AS tgt
USING (
    SELECT d.farher, d.dispodat, GETDATE() [laenderung]
    INNER JOIN inserted i ON i.PrimaryKey = d.PrimaryKey
        AND (i.fahrer <> d.fahrer OR i.beschreibk <> d.beschreik ... )
    UNION
    SELECT i.farher, i.dispodat, GETDATE() [laenderung]
    INNER JOIN inserted i ON i.PrimaryKey = d.PrimaryKey
        AND (i.fahrer <> d.fahrer OR i.beschreibk <> d.beschreik ... )
) AS src (farher, dispodat, laenderung)
ON tgt.farher = src.farher AND tgt.dispodat = src.dispodat
WHEN MATCHED THEN UPDATE SET
    laenderung = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (fahrer,dispodat,laenderung)
    VALUES (src.fahrer, src.dispodat, src.laenderung)