InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 08:56:15 GMT20http://www.sqlservercentral.com/Forums/Topic1371284-392-1.aspxTriggers are a sign of bad SQL; the schema is such a mess that DRI actions cannot be used. So you go to 1970's Sybase procedural code to patch it on the fly. Using local variables in a block of procedural code makes things worse. It says that you are still writing BASIC or COBOL instead of nesting SQL expressions in a declarative language. (I hope that the V_ prefix does not mean “VIEW”). If you get rid of the local fake COBOL registers, and bring the name into ISO-11179 rules with guessing, the body looks like this: ALTER TRIGGER TR_Update_InteractionON DBO.Cust_HistAFTER INSERTASUPDATE Cust_Hist SET note_txt = (SELECT consignment_something FROM V_Loadnote_Carr WHERE load_note_txt IN (SELECT primary_ref FROM INSERTED));Do you see a problem when a set of “consignment_something” is returned? It will not fix into a column. Columns are scholars; that is one of many ways that they are not like records in the file system in your mindset. This is also not the way we keep history tables, either. Thu, 11 Oct 2012 13:31:40 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1371284-392-1.aspxWhat's the UPDATE statement you are using, and is there any reason why it can't include VLoadnoteCarr?Thu, 11 Oct 2012 07:49:42 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1371284-392-1.aspxHi Damion,Have you tried the select and update queries separately. Run them and see if they are working fine.?Thu, 11 Oct 2012 07:42:16 GMTard5karthickhttp://www.sqlservercentral.com/Forums/Topic1371284-392-1.aspxYour trigger has one major flow.When INSERT operation is performed the TRIGGER is fired for an operation, not for every inserted record. The way your trigger is written it will only process your logic for one of the inserted records. You should never read from INSERTED or DELETED into variables in triggers.Here is example of how it should be done properly:[code="sql"]ALTER TRIGGER TR_Update_InteractionON dbo.cust_histAFTER INSERT ASBEGIN SET NOCOUNT ON; UPDATE cust_hist SET notes = v.consignment FROM cust_hist ch JOIN inserted i ON i.tkey = ch.tkey JOIN VLoadnoteCarr v ON v.load_note = i.primary_ref END[/code]Now, if it still doesn't update as expected, you need to analyse JOINs. Are they using relevant key columns?Thu, 11 Oct 2012 02:42:19 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1371284-392-1.aspxI'm trying to use a trigger to update the record that has been created with addtional details, however the statement below does not update the record as desired. I know the trigger is firing as I've modified the @condetails with the word 'Hello' and this worked. I think the problem is to do with the select statement - any ideas?ALTER TRIGGER TR_Update_InteractionON dbo.cust_histAFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @interaction char(22) DECLARE @loadnote char(12) DECLARE @condetails char(100) SELECT @interaction = tkey FROM inserted SELECT @loadnote = primary_ref FROM inserted SELECT @condetails = consignment FROM VLoadnoteCarr WHERE load_note = @loadnote UPDATE cust_hist SET notes = @condetails WHERE tkey = @interaction ENDThu, 11 Oct 2012 02:31:44 GMTDamion