May 9, 2012 at 2:51 am
IF the SSMS configuration is same for both users then it is not an issue with sqlserver. Something else going around their.
May 9, 2012 at 3:17 am
...
When the FedEx software inserts data into the ITEMSENT table, the trigger is, in fact, being ran but the data is not getting updated in the CONTACT2 table as stated earlier. I am verifying this through a phone number that is inserted into the ITEMSENT table.
...
Trigger will execute for all users regardless of anything.
If you cannot see expected results it's all to do with the trigger itself. Let's have a look:
ALTER TRIGGER [dbo].[INSERT_FEDEX_SHIP_DATE]
ON [dbo].[ITEMSENT]
FOR INSERT, UPDATE
AS BEGIN
UPDATE dbo.CONTACT2 SET dbo.CONTACT2.USERDEF24
= CASE
WHEN ISDATE(SHIPDATE) = 1
THEN CAST(SHIPDATE AS DATETIME)
ELSE GETDATE() --?assume today?
END
FROM INSERTED
WHERE INSERTED.ACCOUNTNO = CONTACT2.ACCOUNTNO
END
Your trigger is on insert and update of ITEMSENT table.
When anything inserted or updated in ITEMSENT it tries to update records in CONTACT2.
Question: When the records in CONTACT2 will be updated and when will not (based on the update query in the trigger)?
Answer: Update will only happen when ACCOUNTNO from INSERTED of ITEMSENT will match ACCOUNTNO in CONTACT2 table.
So, when you don't see expected updates, it means that for some reason ACCOUNTNO's do not match.
Can you try to write simple select query for records which missed the update and JOIN between ITEMSENT and CONTACT2 on ACCOUNTNO.
It may be that something is different? Any noise character? SET QUOTED_IDENTIFIER make any difference?
You can also try to log the ACCOUNTNO from INSERTED into some other table and compare it later with ones from CONTACT2 ...
May 9, 2012 at 6:59 am
Eugene,
You hit the nail right on the head. The accountno isn't getting inserted into the ITEMSENT table and is NULL. There is no reference to the accountno in the CONTACT2 table to update it.
Unbelievable that I overlooked that. I appreciate your help.
May 9, 2012 at 8:36 am
Excellent catch, Eugene. (Doh, should have seen that myself)
Glad you figured it out, joshd1807.
May 9, 2012 at 8:49 am
Yeah, the thread title "Trigger works for one user but not..." did catch my attention. As I do hate discrimination of any sort... :hehe:
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply