I need to optimize the first T-SQL update trigger

Question

How to rewrite this update trigger without using a large number of variables?

I wrote my first SQL Server trigger, and it works fine, but I think there should be a simpler solution.

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

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

I assume that you have SQL Server 2008 or higher. You can do this all in one expression without any variables.

Instead of doing all the work to get the variables first and see if they match, you can easily do this as part of the where clause. As noted in the comments, you can have multiple lines as part of inserted and deleted. To make sure that you are working with the same updated string, you need to map the primary key.

To insert or update a row, I use the MERGE . The source of the merge is the union with the where clause above, the top table in the union has an older fahrer, and the bottom is the new one. Like your internal IFs, existing lines are matched by the more distant and distributed options, and also inserted or updated accordingly.

One thing I noticed is that in your example, newfahrer and oldfahrer can be exactly the same so that only one insert or update takes place (i.e. if only bzeit was different). A join should prevent attempts to duplicate data. I believe that a merger would be a mistake if it were.

 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) 

Answer 2

Daniel's answer had a few minor syntax errors. The following code works 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);