|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Update Trigger On Address Table
Posted: 09 Sep 10 8:59 AM
|
There is currently an update trigger on the address table that updates the corresponding address in our ERP system. This trigger frequently appears in my profile traces for long running queries, and as such, I am looking to increase the performance of the trigger. When I run a simple update on the address table in QA (SS2k), ~92% of the cost takes place when "scanning the pseudo-table for "inserted" within a trigger." I will post the contents of the trigger below. Does anyone have any thoughts on how to improve the performance of this? My initial thought is to make the trigger into one update statement, as opposed to checking for individual fields that may have been updated. Also, it was my understanding that SS is looking through the transaction logs when accessing "inserted," would it make more sense to insert the inserted row into a temp table or table variable, and then make the updates from there?
CREATE TRIGGER [tu_ADDRESS_TRIGGER] ON [sysdba].[ADDRESS]
FOR UPDATE
AS
If Exists(Select CUSTID from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID)
BEGIN
IF UPDATE(ADDRESS1)
Update MAS500_App.dbo.tciAddress set ADDRLINE1 = a.ADDRESS1
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(ADDRESS2)
Update MAS500_App.dbo.tciAddress set ADDRLINE2 = a.ADDRESS2
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(ADDRESS3)
Update MAS500_App.dbo.tciAddress set ADDRLINE3 = a.ADDRESS3
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(ADDRESS4)
Update MAS500_App.dbo.tciAddress set ADDRLINE4 = a.ADDRESS4
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(CITY)
Update MAS500_App.dbo.tciAddress set CITY = a.CITY
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(STATE)
Update MAS500_App.dbo.tciAddress set STATEID = a.STATE
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(POSTALCODE)
Update MAS500_App.dbo.tciAddress set POSTALCODE = a.POSTALCODE
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(COUNTRY)
Update MAS500_App.dbo.tciAddress set COUNTRYID = a.COUNTRY
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
IF UPDATE(ADDRESS1) OR UPDATE(POSTALCODE) or UPDATE(ADDRESS2) or UPDATE(ADDRESS3) or UPDATE(CITY) or UPDATE(STATE)
Update MAS500_App.dbo.tciAddress set CRMADDRID = a.ADDRESSID
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
END
|
|
|
| |
|
Re: Update Trigger On Address Table
Posted: 09 Sep 10 10:31 AM
|
This is what I have changed the trigger to,
If Exists(Select CUSTID from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID)
BEGIN
IF UPDATE(MODIFYDATE)
Update MAS500_App.dbo.tciAddress set ADDRLINE1 = a.ADDRESS1,
ADDRLINE2 = a.ADDRESS2,
ADDRLINE3 = a.ADDRESS3,
ADDRLINE4 = a.ADDRESS4,
CITY = a.CITY,
STATEID = a.STATE,
POSTALCODE = a.POSTALCODE,
COUNTRYID = a.COUNTRY,
CRMADDRID = a.ADDRESSID
from DL_Acct_Ref d,
Account c,
Address a,
MAS500_App.dbo.tarCustomer t,
MAS500_App.dbo.tciAddress i,
Inserted k
where d.Acct_ID = t.CustID
and t.dfltBilltoaddrkey = i.AddrKey
and d.AccountID = c.AccountID
and c.AddressID = a.AddressID
and t.CompanyID = 'QMC'
and a.ADDRESSID = k.ADDRESSID
END
The original trigger seemed ridiculous. If a city or state has been updated, then obviously the address information has also been updated. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|