Need some help figuring out my first trigger

Question

Post reply

Need some help figuring out my first trigger

joshd 1807

Default port

Points: 1499
More actions
April 4, 2012 at 8:31 am

#271493

I'm creating my first trigger to update a field in another table when a new record is created in another table.
Here's what I have. We're using FEDEX software to grab data from a table in our CRM. It inserts a ship date into the ITEMSENT table alond with an accountno taken from our CRM.
I have a field in our CRM that shows the ship date, but it is manual updated by another user. I want ITEMSENT.SHIPDATE to automatically be updated to the CONTACT.SHIPDATE when the new record is created in the ITEMSENT field.
The one problem I have is that the ITEMSENT.SHIPDATE is not a datetime field. It's actually a varchar field, which I am converting to a datetime on the fly.
The trigger doesn't update the CONTACT.SHIPDATE field as expected. Could I get some insight on why this is happening?
CREATE TRIGGER INSERT_FEDEX_SHIP_DATE
ON DBO.ITEMSENT
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS BEGIN
UPDATE dbo.CONTACT SET dbo.CONTACT.SHIPDATE = CAST(SHIPDATE AS DATETIME)
FROM ITEMSENT
WHERE ITEMSENT.ACCOUNTNO = CONTACT.ACCOUNTNO
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

just a basic logic error;

a trigger uses the virtual INSERTED and DELETED tables, not the whole table itself.

using the INSERTED table will update only the items that were affected, and not ALL items.

for the date, would you need to test if the string is a valid date or not?

CREATE TRIGGER INSERT_FEDEX_SHIP_DATE

ON DBO.ITEMSENT

FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */

AS BEGIN

UPDATE dbo.CONTACT SET dbo.CONTACT.SHIPDATE

= CASE

WHEN ISDATE(SHIPDATE ) = 1

THEN CAST(SHIPDATE AS DATETIME)

ELSE GETDATE() --?assume today?

END

FROM INSERTED

WHERE INSERTED.ACCOUNTNO = CONTACT.ACCOUNTNO

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

joshd 1807 Default port Points: 1499 More actions · Answer 2

I don't think I'd need to test for a valid date as the FedEx software would take care of that itself.

I'll give your suggestion a try and let you know how it pans out.

Thanks for the help!

CapnHector SSCoach Points: 16143 More actions · Answer 3

joshd 1807 (4/4/2012)
I don't think I'd need to test for a valid date as the FedEx software would take care of that itself.
I'll give your suggestion a try and let you know how it pans out.
Thanks for the help!

Personally i would still test for a valid date as i dont trust third party software. they make a change and break things for me to often. (not saying fedex would do that but i like to be sure)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

joshd 1807 Default port Points: 1499 More actions · Answer 4

Haha....good point capn.hector

Lowell,

Your suggestion worked beautifully.

I appreciate the help you guys!

joshd 1807 Default port Points: 1499 More actions · Answer 5

I had an issue this morning...

...two users have the FedEx software. One can use the software and the trigger trips each and every time while the other user cannot get anything to trigger.

Both users have the same SQL authentication (datareader and datawriter) and the FedEx software on both PC's is set up using the same ODBC authentication and name.

Any ideas on how to debug this one?