question

Noonies avatar image
Noonies asked

CREATE TRIGGER

I have created a trigger on OrderInquiry table to pull the MAX(CustomerID) column in ContactInfo table. The UPDATE works but I'm missing something in my Trigger. Basically the web programmer has left the CustomerID column on ContactInfo NULL because he could not insert the CUstomerID from ContactInfo during his web page order form submit. As a work around due to deadlines, I would like to update the CustomerID column based on the record insert from the roder form into the ContactInfo table. Here is what I have: CREATE TRIGGER updateCustomerID on [dbo].[OrderInquiry] FOR INSERT, UPDATE AS BEGIN UPDATE OrderInquiry SET CustomerID = (SELECT MAX(CustomerID) FROM ContactInfo ) WHERE InquiryID = (SELECT MAX(INquiryID) FROM OrderInquiry ) END; I know this is not the best way to do this but it is for a school project and since he doesnt know how to write the code in ASP I figure this would be an okay work around. Any information is greatly appreciated. Thanks in advance.
triggerhomework
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
@Noonies, what happens if the MAX(CustomerId) and MAX(InquiryID) do not match to the record that causes the trigger to fire? You would be associating the OrderInquiry to the wrong customer and inquiry! This problem may not seem bad in a small load test environment, but is a bad design practice that you need to be aware of. Think about the possibility of this sort of clash happening if two separate users are updating an OrderInquiry, this would silently break the relations and probably only be discovered a long time after the initial corruption started. If I were you, I would make sure that the developer fixes his/her code. Bad data in = Bad data out!
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@WilliamD - I totally understand that this is not how it should be done. This is for a class project so will not be implemented in another environment. I need to do this because the developer on my team for this project does not know how to code for this. Nor do I from lack of experience with ASP .NET. :( I have modified the trigger for now. I will see what I can do on the ASP .NET page and some research on how to code for this. I'm sure with some extra time I can figure this out.
0 Likes 0 ·
@Noonies - it is not just a matter of not how it should be done. The trigger doesn't do what you want it to do! The way it is currently written, it will update the OrderInquiry record that matches the MAX(InquiryID) - this may not be the record that is being updated and causing the trigger to fire! Picture this: You have two records in the OrderInquiry table and in the ContactInfo tables (so MAX(InquiryId) = 2 and MAX(CustomerId)=2). You then add a new customer to the ContactInfo table. The customer who had the first OrderInquiry record calls and changes something. This is then updated and the trigger fires. The trigger then finds the MAX(CustomerId) (which is now 3) and sets that as the customer for the OrderInquiry with the highest InquiryID (which is 2). Now you have a broken relation - The new customer now has the OrderInquiry from CustomerID 2 associated to it. You only realise this after a week and all inquiries are now suspect. What do you do to fix this?
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
@Noonies, note that if you alter also the `ContactInfo` table, it has to be modified prior the `OrderInquiry` table. Another problem is, that your trigger will update only the `OrderInquiry` with the `Max Inquiry` no matter what Inquiry row you will updating. Also related to the trigger, note, that the triggers operate upon sets (this means if your DML statement updates several records, the trigger will be fired only once) and the new data are available in the `inserted` virtual table inside the trigger. and the original data in the `deleted` virtual table. Check the [Using the inserted and deleted Tables][1] on MSDN [1]: http://msdn.microsoft.com/en-us/library/ms191300.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.