My update triggers fired even no record is updated!
-
Hi experts,
I created 2 update triggers in 2 tables few weeks ago as following, the reason I created the triggers is that I want to know which record was updated in 2 tables (UPR00100 and UPR00102) when user used Great Plains (Microsoft financial system) to modify any employee's information. Then one of my weekly reports will look for this update_ind=1 (in UPR00100) to export the file to bank to notify any changes for our employee information.
However, when I checked the table, there's more than 500 employee's update_ind set to 1, and user said she only modified less than 30 employees. I am wondering if it's possible that even when user just view the records from the finanical system, it will fire this update trigger????
Is there anyway around it? I only want to set update_ind=1 when there's really a record updated! Thanks.
-------------------------------------------------------
Create Trigger Tg_UpdateIndication On UPR00100
For Update
AS
IF UPDATE (LASTNAME) or update (FRSTNAME) or update (MIDLNAME) or update (BRTHDATE) or update (DEPRTMNT)
BEGIN
update UPR00100
set Update_Ind=1
from inserted
where (inserted.EmployID = UPR00100.EmployID)
END
-------------------------------------------------
CREATE Trigger Tg_UpdateInd On UPR00102
For Update
AS
IF UPDATE (Address1) or update (Address2) or update (CITY) or Update (State) or update (ZIPCODE) or update (Phone1)
BEGIN
update UPR00100
set Update_Ind=1
from inserted
where (inserted.EmployID = UPR00100.EmployID ) and (inserted.ADRSCODE=UPR00100.ADRSCODE)
END
-
Try this :
If Update(?)....
begin
Update UPR set UPR.Update_Ind = 1 from Inserted Ins inner join dbo.UPR00100 UPR on Ins.EmployID = UPR.EmployID and Ins.ADRSCODE = UPR.ADRSCODE
WHERE (Ins.Adress1 UPR.Adress1 or Ins.Address2 UPR.Adress2 or...)
end
the where condition will ignore updates that don't make any changes in the data. Also keep in mind that sql server uses short circuiting so I'd put the fields that are most likely to be updated in the front of the list (might not make a huge difference here since there's only a few updates/day but if you have huge loads of transaction it can be a great boost.).
-
Some rules for you:
1. At the begining of the trigger check for @@rowcount
2. Use the if UPDATE(col) on separated columns. Don't mix them up
3. in the where clause compare old and new vaules and if they permit nulls then account for that also
as an example:
Create Trigger Tg_UpdateIndication On UPR00100
For Update
AS
IF @@ROWCOUNT = 0
RETURN
-- assuming EmployID is primary Key
IF UPDATE (LASTNAME)
begin
update UPR set Update_Ind=1
from
UPR00100 UPR
join
inserted new on new.EmployID = UPR.EmployID
join
deleted old on old.EmployID = UPR.EmployID
where
new.LASTNAME <> old.LASTNAME
or (new.LASTNAME IS NULL and old.LASTNAME IS NOT NULL )
or (new.LASTNAME IS NOT NULL and old.LASTNAME IS NULL )
end
HTH
* Noel -
Thanks for your help. But I still have problem with my 2nd trigger.
The 2nd trigger will update the 1st table (set Update_Ind=1) when there's a update on 2nd table (UPR00200). So I guess the join is not correct,
join
dbo.UPR00100 UPR1 on UPR1.EmployID = UPR2.EmployID and UPR1.ADRSCODE = UPR2.ADRSCODE
but when I apply the changes in the 2nd trigger, it didn't throw me any errors.
The 1st table is the master, 1 employee can have multiple address, so I have to look up both employid and adrscode.
Here is the 2nd trigger: Can you please point me out where it goes wrong? Thanks.
CREATE Trigger Tg_UpdateInd On UPR00102
For Update
AS
IF @@ROWCOUNT = 0
RETURN
IF UPDATE (Address1) or update (Address2) or update (CITY) or Update (State) or update (ZIPCODE) or update (Phone1)
BEGIN
update UPR00100
set Update_Ind=1
from
UPR00200 UPR2
join
inserted new on new.EmployID = UPR1.EmployID
join
deleted old on old.EmployID = UPR1.EmployID
join
dbo.UPR00100 UPR1 on UPR1.EmployID = UPR2.EmployID and UPR1.ADRSCODE = UPR2.ADRSCODE
where
new.Address1 <> old.Address1
or (new.Address2 <> old.Address2 )
or (new.CITY <> old.CITY )
or (new.State <> old.State )
or (new.ZIPCODE <> old.ZIPCODE )
or (new.Phone1 <> old.Phone1 )
END
-
Do you want to update UPR00200 or UPR00100 cause this
BEGIN
update UPR00100
will update table one instead of 2.
-
>> The 2nd trigger will update the 1st table (set Update_Ind=1) when there's a update on 2nd table (UPR00200). So I guess the join is not correct,
It's updating the 1st table because that's what you're explicitly telling it to do:
>>update UPR00100
Your code is explcitly updating UPR00100 (the 1st table) and not UPR0020.
-
To answer the second trigger I need:
How 101 relates to 102
One record on 101 to many on 102 ?
or
One record on 102 to many on 101?
and through what keys?
can you put some example data?
* Noel -
Yes, the Update_Ind is only at the 1st table (UPR00100). If there's any updates in 2nd table (UPR00200), the trigger should set Update_Ind=1, and nothing to do with 2nd table but only checking the updates.
1st table(UPR00100) is the master employee table, not duplicate records, employid is the primary key.
example:
employID Lname FName MidName ADRSCODE
999999999 Doe John Premary
2nd table(UPR00200) is the employee information table. 1 employee can have more than 1 rows because the they can have up to 3 addresses. The employid and ADRSCODE are keys.
employid ADRSCODE ADDRESS1 Phone
999999999 Primary 123 Elm St. 6309093489
999999999 Secondary 333 Main St. 3128887909
-
Update UPR00100
set Update_Ind=1
from UPR00100 As u1
Where Exists (
Select *
From inserted As new
Inner Join deleted As old
On ( new.EmployID = old.EmployID and
new.AdrsCode = old.AdrsCode )
Where new.EmployID = u1.EmployID
And ( new.Address1 <> old.Address1
or (new.Address2 <> old.Address2 )
or (new.CITY <> old.CITY )
or (new.State <> old.State )
or (new.ZIPCODE <> old.ZIPCODE )
or (new.Phone1 <> old.Phone1 ) )
)
-
here you go!
update UPR1
set Update_Ind=1
from
UPR00100 UPR1
join
inserted new on new.EmployID = UPR1.EmployID and new.ADRSCODE = UPR1.ADRSCODE
join
deleted old on old.EmployID = UPR1.EmployID and old.ADRSCODE = UPR1.ADRSCODE
where
new.Address1 <> old.Address1
or (new.Address2 <> old.Address2 )
or (new.CITY <> old.CITY )
or (new.State <> old.State )
or (new.ZIPCODE <> old.ZIPCODE )
or (new.Phone1 <> old.Phone1 )
Again make sure you check for NULLs
HTH
* Noel -
There are 2 design issues here, and something we haven't been told about.
1st off, there are cardinality issues, if 2 or more addresses get updated in a batch, you only want to update the parent record *once*. Hence the Exists () in my code.
Secondly, AdrsCode is supposed to be the column which distinguishes the multiple addresses in the child table so what is it also doing in the parent table ?
Including it in the join gurantees that the trigger won't work as designed, because if you update the Secondary address, the join won't locate the parent.
-
1st off, there are cardinality issues, if 2 or more addresses get updated in a batch, you only want to update the parent record *once*. Hence the Exists () in my code
True , just it does not makes sense to update 2 addresses of the same person at once (i took a shortcut based on that
)Secondly, AdrsCode is supposed to be the column which distinguishes the multiple addresses in the child table so what is it also doing in the parent table ?
Including it in the join gurantees that the trigger won't work as designed, because if you update the Secondary address, the join won't locate the parent.
AGREED
I do concur with you also that there are either design flaws or missing information in the post. We just try to help with the minimum available but in this case more is probably needed.
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply