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.
@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!
@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] on MSDN :