Help With Instead Of Update Trigger
-
I have an update trigger that, when enabled on the table, causes an update query to never finish (I've let it run as much as 24 minutes). If a single record is being updated it works fine. When I do a mass update, every record from another table matching a foreign key here (about 11,500), I have the problem. If I disable the trigger the update works fine, but I don't get the intended results in the table.
Background: I must extract a bunch of information from HR to put into AP so that AP can send out expense checks to employees. I extract the necessary data from several different fields in HR and put that data into a single table set up for this purpose. The Update trigger is supposed to look at the values being updated for certain fields, and if the value is different from the previous value, update that field, put the old value in another field, and update the LastUpdated date field. The fields involved are name or address fields that may change.
Trigger contents:
CREATE TRIGGER trgUpdateVendorList ON [dbo].[laPSVendorExport]
Instead of UPDATE
AS
--In a case where this is an update, we must check to see if certain fields are updated and flag them when necessary.
--Name changes must be flagged (insert current date into the NameChanged field).
If Update(FirstName)
Update PS
Set OldFirstName = D.FirstName,
FirstName = I.FirstName,
NameChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.FirstName <> D.FirstName
If Update(LastName)
Update PS
Set OldLastName = D.LastName,
LastName = I.LastName,
NameChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.LastName <> D.LastName
If Update(MiddleName)
Update PS
Set OldMiddleName = D.MiddleName,
MiddleName = I.MiddleName,
NameChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.MiddleName <> D.MiddleName
--Address changes must be flagged (insert current date into the AddressChanged field).
If Update(Street1)
Update PS
Set OldStreet1 = D.Street1,
Street1 = I.Street1,
AddressChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.Street1 <> D.Street1
If Update(Street2)
Update PS
Set OldStreet2 = D.Street2,
Street2 = I.Street2,
AddressChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.Street2 <> D.Street2
If Update(City)
Update PS
Set OldCity = D.City,
City = I.City,
AddressChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.City <> D.City
If Update(State)
Update PS
Set OldState = D.State,
State = I.State,
AddressChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.State <> D.State
If Update(Zip)
Update PS
Set OldZip = D.Zip,
Zip = I.Zip,
AddressChanged = GetDate()
From Deleted D, Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
And I.Zip <> D.Zip
--Just update these fields inf an update comes through, no tracking needed.
If Update(Prefix)
Update PS
Set PreFix = I.Prefix
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(Suffix)
Update PS
Set Suffix = I.Suffix
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(Email)
Update PS
Set Email = I.Email
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(DateHired)
Update PS
Set DateHired = I.DateHired
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(TermDate)
Update PS
Set TermDate = I.TermDate
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(UnitNumber)
Update PS
Set UnitNumber = I.UnitNumber
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(SupervisorFirst)
Update PS
Set SupervisorFirst = I.SupervisorFirst
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(SupervisorLast)
Update PS
Set SupervisorLast = I.SupervisorLast
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(SupervisorEmpNum)
Update PS
Set SupervisorEmpNum = I.SupervisorEmpNum
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(NetworkAccount)
Update PS
Set NetworkAccount = I.NetworkAccount
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(NameChanged)
Update PS
Set NameChanged = I.NameChanged
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(AddressChanged)
Update PS
Set AddressChanged = I.AddressChanged
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
The Update query (where clause omitted)
Update PS
Set
PS.FirstName = EB.ebFirstName,
PS.LastName = EB.ebLastName,
PS.MiddleName = EB.ebMiddleName,
PS.EmpNumber = EB.ebClock,
PS.Prefix = EP.epPrefix,
PS.Suffix = EP.epSuffix,
PS.Email = EP.epEmail,
PS.Street1 = EP.epStreet1,
PS.Street2 = EP.epStreet2,
PS.City = EP.epCity,
PS.State = EP.epState,
PS.Zip = EP.epZip,
PS.DateHired = RTrim(IsNull(Convert(Char(10),EE.eeDateLastHire, 110), Space(10))),
PS.TermDate = RTrim(IsNull(Convert(Char(10), EE.eeTermDate, 110), Space(10))),
PS.UnitNumber = EJ.ejDivision,
PS.SupervisorFirst = IsNull(EB1.ebFirstName, ''),
PS.SupervisorLast = IsNull(EB1.ebLastName, ''),
PS.SupervisorEmpNum = IsNull(EB1.ebClock, ''),
PS.NetworkAccount = IsNull(D.Network_Account, '')
From Ebase EB
Inner Join ePerson EP On EB.ebFlxID = EP.epFlxIDeb
Inner Join eEmploy EE On EB.ebFlxID = EE.eeFlxIDeb
Inner Join eJob EJ On EB.ebFlxID = EJ.ejFlxIDeb
Left Join eBase EB1 On EJ.ejSupervisorFlxIDeb = EB1.ebFlxID
Left Join svariINDB03.Directory_New.dbo.Person D On EB.ebClock = D.Employee_Num
Inner Join laPSVendorExport PS On EB.ebClock = PS.EmpNumber
Any suggestions on why this will not let a "mass" update work will be appreciated.
Thanks,
Chris
-
I can think of 2 things, 1st you could try updating 100 rows at time to see if the update trigger actually works and if the time required is lower. And 2nd this query can be optimized a lot :
If Update(Prefix)
Update PS
Set PreFix = I.Prefix
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(Suffix)
Update PS
Set Suffix = I.Suffix
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(Email)
Update PS
Set Email = I.Email
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(DateHired)
Update PS
Set DateHired = I.DateHired
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(TermDate)
Update PS
Set TermDate = I.TermDate
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(UnitNumber)
Update PS
Set UnitNumber = I.UnitNumber
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(SupervisorFirst)
Update PS
Set SupervisorFirst = I.SupervisorFirst
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(SupervisorLast)
Update PS
Set SupervisorLast = I.SupervisorLast
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(SupervisorEmpNum)
Update PS
Set SupervisorEmpNum = I.SupervisorEmpNum
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(NetworkAccount)
Update PS
Set NetworkAccount = I.NetworkAccount
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(NameChanged)
Update PS
Set NameChanged = I.NameChanged
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
If Update(AddressChanged)
Update PS
Set AddressChanged = I.AddressChanged
From Inserted I, laPSVendorExport PS
Where PS.EmpNumber = I.EmpNumber
skip the if update() and do 1 statement that checks it all at once like :
Update PS set AddressChanged = I.AdressChanded, Col1 = I.Col1, (Update all fields) from Inserted I inner join laPSVendorExport PS on I.EmpNumber = PS.EmpNumber where (Col1 I.Col1 or Col2 I.Col2 ....)
this will be much faster because you will do the inner join only once and the update checks will be minimal.
-
Thanks for the reply. I used some of your suggestions and did some calculations to find that I wasn't waiting long enough for the query to complete, by about 15 minutes. I made some adjustments to the update query against that table, to only update stuff that is different, and it runs much faster now. Below is the final of the trigger, which works great if you don't try to feed it 11,500 records at one sitting.
Thanks,
Chris
CREATE TRIGGER trgAddOrUpdateVendorList ON dbo.laPSVendorExport
Instead of Insert, Update
As
--Do not insert data for Employee Number that already exists
If (Not Exists(Select PS.EmpNumber From laPSVendorExport PS
Inner Join Inserted I On PS.EmpNumber = I.EmpNumber ))
Begin
Insert Into laPSVendorExport
(FirstName, LastName, MiddleName, EmpNumber, Prefix, Suffix, Email,
Street1, Street2, City, State, Zip, DateHired, TermDate, UnitNumber,
SupervisorFirst, SupervisorLast, SupervisorEmpNum, NetworkAccount)
Select
FirstName, LastName, MiddleName, EmpNumber, Prefix, Suffix, Email,
Street1, Street2, City, State, Zip, DateHired, TermDate, UnitNumber,
SupervisorFirst, SupervisorLast, SupervisorEmpNum, NetworkAccount
From Inserted
End
Else --In a case where this is an update, we must check to see if certain fields are updated and flag them when necessary.
--Name changes must be flagged (insert current date into the NameChanged field).
Update PS
Set OldFirstName = D.FirstName,
FirstName = I.FirstName,
NameChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.FirstName <> D.FirstName
Update PS
Set OldLastName = D.LastName,
LastName = I.LastName,
NameChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.LastName <> D.LastName
Update PS
Set OldMiddleName = D.MiddleName,
MiddleName = I.MiddleName,
NameChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.MiddleName <> D.MiddleName
--Address changes must be flagged (insert current date into the AddressChanged field).
Update PS
Set OldStreet1 = D.Street1,
Street1 = I.Street1,
AddressChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.Street1 <> D.Street1
Update PS
Set OldStreet2 = D.Street2,
Street2 = I.Street2,
AddressChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.Street2 <> D.Street2
Update PS
Set OldCity = D.City,
City = I.City,
AddressChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.City <> D.City
Update PS
Set OldState = D.State,
State = I.State,
AddressChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.State <> D.State
Update PS
Set OldZip = D.Zip,
Zip = I.Zip,
AddressChanged = GetDate()
From Deleted D Inner Join Inserted I On D.EmpNumber = I.EmpNumber
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.Zip <> D.Zip
--Just update these fields if an update comes through, no tracking needed.
Update PS
Set
PS.PreFix = I.Prefix,
PS.Suffix = I.Suffix,
PS.Email = I.Email,
DateHired = I.DateHired,
TermDate = I.TermDate,
UnitNumber = I.UnitNumber,
SupervisorFirst = I.SupervisorFirst,
SupervisorLast = I.SupervisorLast,
SupervisorEmpNum = I.SupervisorEmpNum,
NetworkAccount = I.NetworkAccount
From Inserted I
Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
-
I think you could make it even faster... I noticed that you are joining deleted to inserted on every update query. What you are doing is asking sql server to join to tables of 11500 lines 9 times in a row. You could avoid this using a table variable (create table variable, insert the joined inserted and deleted tables, then join on the Table variable).
but even faster than that.. couldn't you just do this instead :
Update PS
Set OldFirstName = D.FirstName,
FirstName = I.FirstName,
NameChanged = GetDate()
From Inserted I Inner Join laPSVendorExport PS On PS.EmpNumber = I.EmpNumber
Where I.FirstName PS.FirstName
Since you want to update only the lines that are updated, you can check in the original table against the inserted table to see if anything has changed (which makes about 100 000 less lines to join to 🙂 )
if that table gets a lot of hits you may notice a difference even in production evironnement (not only with 11500 records)
-
BTW I would be interested to know what's the final speed compared to the original trigger you posted... I curious to know how much of a difference that makes (the tests I made gave from 4 to 10 times faster depending on the queries).
-
Remi,
What you suggested about is actually what I am doing now. I join my update query to the destination table and then compare the source and destination fields for inequality before I update them. I am trying to work out some trash data problems now, but it returns 2 records in the update in about 13 seconds. This should run faster, but I cannot test it until I work out this trash data. The 2 records it returns are for the same user, and look identical, meaning one of my joined tables has bad data.
Thanks,
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply