SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General / Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?: / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:39:33 GMT20RE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspx[quote][b]Tava (11/12/2012)[/b][hr][quote][b]briancampbellmcad (11/12/2012)[/b][hr]Both the PO_Number and HeatTicketNumber are already Varchar as the table datatype... so (?) no need for conversion (?)...I ran the code above and now have a SQL error popping up from my ASp.net app:String or binary data would be truncated.The statement has been terminated. [/quote]Check the datatype size of the VARCHAR columns compared to the PO_Number & HeatTicketNumber? Chances are one of those 2 columns is greater in length then what you have specified. That's what the error you got normally means? [/quote]I agree; the size of the field tblSWJournal.Ledger has to be at least as large as the maximum size of PO_Number & HeatTicketNumber & the separator ':'in my example, that would be 61 characters (two varchar(30)'s plus the semicolon); you know what your real field sizes are; you'll need to change the tblSWJournal.Ledger size most likely.review your data sized and fix whatever is invalid.Tue, 13 Nov 2012 05:12:29 GMTLowellRE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspx[quote][b]Tava (11/12/2012)[/b][hr][quote][b]briancampbellmcad (11/12/2012)[/b][hr]Both the PO_Number and HeatTicketNumber are already Varchar as the table datatype... so (?) no need for conversion (?)...I ran the code above and now have a SQL error popping up from my ASp.net app:String or binary data would be truncated.The statement has been terminated. [/quote]Check the datatype size of the VARCHAR columns compared to the PO_Number & HeatTicketNumber? Chances are one of those 2 columns is greater in length then what you have specified. That's what the error you got normally means? [/quote]Thought i would help you as I don't know you're exact skill level. Do a Length check on your data.[code="sql"]SELECT LEN(ColumnName) AS StringLengthFROM TableNameORDER BY StringLength DESC[/code]What ever the biggest size it is needs to be less than the specified size on the VARCHAR(X)Mon, 12 Nov 2012 18:16:49 GMTTavaRE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspx[quote][b]briancampbellmcad (11/12/2012)[/b][hr]Both the PO_Number and HeatTicketNumber are already Varchar as the table datatype... so (?) no need for conversion (?)...I ran the code above and now have a SQL error popping up from my ASp.net app:String or binary data would be truncated.The statement has been terminated. [/quote]Check the datatype size of the VARCHAR columns compared to the PO_Number & HeatTicketNumber? Chances are one of those 2 columns is greater in length then what you have specified. That's what the error you got normally means? Mon, 12 Nov 2012 18:09:31 GMTTavaRE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspxBoth the PO_Number and HeatTicketNumber are already Varchar as the table datatype... so (?) no need for conversion (?)...I ran the code above and now have a SQL error popping up from my ASp.net app:String or binary data would be truncated.The statement has been terminated. Mon, 12 Nov 2012 15:02:30 GMTbriancampbellmcadRE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspxlooked a little deeper, this is a syntactically correct version that only inserts the rows that have INSERTED.Transaction_Type = 'From Bulk Assignment'[code]GOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[trPopulateJournalEntries]ON [dbo].[tblTransactions]AFTER INSERTAS BEGIN INSERT INTO tblSWJournal (tblSWJournal.Ledger,tblBulkPurchases.Amt) SELECT CONVERT(VARCHAR(30), INSERTED.HeatTicketNumber) + ':' + CONVERT(VARCHAR(30), INSERTED.PO_Number), INSERTED.Unit_Price FROM INSERTED WHERE INSERTED.Transaction_Type = 'From Bulk Assignment' ENDGO [/code]Mon, 12 Nov 2012 14:10:39 GMTLowellRE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspxIn general it seems to not like something else that keeps popping up:Msg 102, Level 15, State 1, Procedure trPopulateJournalEntries, Line 11Incorrect syntax near '('.Mon, 12 Nov 2012 13:58:38 GMTbriancampbellmcadRE: Concatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspxit would be simple concatenation;if the types happen to be integers, you might need to convert them to varchars:[code]INSERT INTO tblSWJournal(tblSWJournal.Ledger,tblBulkPurchases.Amt)SELECT CONVERT(varchar(30),INSERTED.HeatTicketNumber) + ':' + CONVERT(VARCHAR(30),INSERTED.PO_Number),INSERTED.Unit_PriceFROM INSERTED[/code]Mon, 12 Nov 2012 13:56:17 GMTLowellConcatenate IN TRIGGER two fields into one in this line of the TRIGGER T-SQL?:http://www.sqlservercentral.com/Forums/Topic1383862-391-1.aspxCan anyone show how to concatenate two fields into one in this line of the TRIGGER T-SQL?:INSERTED.(HeatTicketNumber,PO_Number)>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.GOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[trPopulateJournalEntries]ON [dbo].[tblTransactions]AFTER INSERTAS BEGINIF (SELECT Transaction_Type FROM INSERTED) = 'From Bulk Assignment'INSERT INTO tblSWJournal(tblSWJournal.Ledger, tblBulkPurchases.Amt)SELECT INSERTED.(HeatTicketNumber,PO_Number) INSERTED.Unit_Price FROM INSERTEDENDGOMon, 12 Nov 2012 13:47:41 GMTbriancampbellmcad