InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:42:36 GMT20http://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxThank you... all is working!Tue, 15 Jan 2013 08:06:06 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxThank you... all is working!Tue, 15 Jan 2013 08:05:50 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxI think it quite likely that it doesn't like you using BEGIN without an END...:-PMon, 14 Jan 2013 17:40:51 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxyou may need to qualify as I mentioned:[code="sql"]USE [TrackIT]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trUpdateBulkPurchases]ON [dbo].[tblTransactions]AFTER UPDATEASBEGINUPDATE tblBulkPurchasesSETPO_Number = i.PO_Number,Quantity = i.Quantity,Unit_Price = i.Unit_Price,Software_Description = i.Software_Description,PO_Date = i.PO_Date,PurchaseCostCenter = i.PurchaseCostCenter,HeatTicketNumber = i.HeatTicketNumber,PurchaseAccount = i.PurchaseAccount,Transaction_Date = i.Transaction_Date,SoftwareShortName = i.SoftwareShortNameFROM INSERTED iinner join tblBulkPurchaseson tblBulkPurchases.Transaction_Number = i.Transaction_NumberWHERE tblBulkPurchases.Transaction_Type = 'Bulk Purchase'[/code]Mon, 14 Jan 2013 15:00:06 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxCorrect... the asterick was just to show in the post where the problem was. There's no asterick in the code.Mon, 14 Jan 2013 14:34:58 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxDo you have an asterisk in your code? I thought you were using the to mark the place you were writing about.No asterisks needed in your code.Mon, 14 Jan 2013 14:32:40 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspx[quote][b]briancampbellmcad (1/14/2013)[/b][hr]Like this? (Still doesn't like near where the * is):...WHERE Transaction_Type = 'Bulk Purchase' *GO[/quote]Yes, like that but no asterix. You won't be using an * here.Mon, 14 Jan 2013 13:56:33 GMTtoddasdhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxWHERE Transaction_Type = 'Bulk Purchase' Msg 102, Level 15, State 1, Procedure trUpdateBulkPurchases, Line 21Incorrect syntax near 'Bulk Purchase'.Mon, 14 Jan 2013 13:55:10 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxWhat's the error? If it's an ambiguous column, you need to specify from which table (The base one or INSERTED) you are specifying in the WHERE clause.Mon, 14 Jan 2013 13:39:56 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxLike this? (Still doesn't like near where the * is):USE [TrackIT]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trUpdateBulkPurchases]ON [dbo].[tblTransactions]AFTER UPDATEASBEGINUPDATE tblBulkPurchasesSETPO_Number = i.PO_Number,Quantity = i.Quantity,Unit_Price = i.Unit_Price,Software_Description = i.Software_Description,PO_Date = i.PO_Date,PurchaseCostCenter = i.PurchaseCostCenter,HeatTicketNumber = i.HeatTicketNumber,PurchaseAccount = i.PurchaseAccount,Transaction_Date = i.Transaction_Date,SoftwareShortName = i.SoftwareShortNameFROM INSERTED iinner join tblBulkPurchaseson tblBulkPurchases.Transaction_Number = i.Transaction_NumberWHERE Transaction_Type = 'Bulk Purchase' *GOMon, 14 Jan 2013 12:12:19 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxSorry, wasn't reading well. The problem is that you have a malformed UPDATE statement.[code="sql"]CREATE TRIGGER [dbo].[trUpdateBulkPurchases]ON [dbo].[tblTransactions]AFTER UPDATEASBEGINUPDATE tblBulkPurchases set PO_Number = i.PO_Number , Quantity = i.quantity...FROM INSERTED iinner join tblBulkPurchaseson tblBulkPurchases.Transaction_Number = i.Transaction_NumberWHERE Transaction_Type = 'Bulk Purchase' GO[/code]Mon, 14 Jan 2013 12:02:20 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxKinda like your way... but the compiler doesn't like where I've placed astericks:USE [TrackIT]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trUpdateBulkPurchases]ON [dbo].[tblTransactions]AFTER UPDATEAS BEGINUPDATE tblBulkPurchases*(tblBulkPurchases.PO_Number, tblBulkPurchases.Quantity, tblBulkPurchases.Transaction_Number, tblBulkPurchases.Quantity_Remaining, tblBulkPurchases.Unit_Price, tblBulkPurchases.Software_Description, tblBulkPurchases.PO_Date, tblBulkPurchases.PurchaseCostCenter, tblBulkPurchases.HeatTicketNumber, tblBulkPurchases.PurchaseAccount, tblBulkPurchases.Transaction_Date, tblBulkPurchases.Transaction_Type, tblBulkPurchases.SoftwareShortName)SELECT i.PO_Number, i.Quantity, i.Transaction_Number, i.Quantity, i.Unit_Price, i.Software_Description, i.PO_Date, i.PurchaseCostCenter, i.HeatTicketNumber, i.PurchaseAccount, i.Transaction_Date, i.Transaction_Type, i.SoftwareShortNameFROM INSERTED i inner join tblBulkPurchases on tblBulkPurchases.Transaction_Number = i.Transaction_Number WHERE Transaction_Type = 'Bulk Purchase' *GOMon, 14 Jan 2013 11:56:49 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxINSERTED.___ is fine. I'd do this, but your way works:[code="sql"]UPDATE tblBulkPurchases(tblBulkPurchases.PO_Number,tblBulkPurchases.Quantity,tblBulkPurchases.Transaction_Number,tblBulkPurchases.Quantity_Remaining,tblBulkPurchases.Unit_Price,tblBulkPurchases.Software_Description,tblBulkPurchases.PO_Date,tblBulkPurchases.PurchaseCostCenter,tblBulkPurchases.HeatTicketNumber,tblBulkPurchases.PurchaseAccount,tblBulkPurchases.Transaction_Date,tblBulkPurchases.Transaction_Type,tblBulkPurchases.SoftwareShortName)SELECTi.PO_Number,i.Quantity,i.Transaction_Number,i.Quantity,...FROM INSERTED i inner join tblBulkPurchases on tblBulkPurchases.PO = i.po WHERE Transaction_Type = 'Bulk Purchase'[/code]Mon, 14 Jan 2013 11:28:54 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxCorrect... do need to associate by PO = PO... thanks.... is the word INSERTED.____ appropriate to use?Mon, 14 Jan 2013 11:18:00 GMTbriancampbellmcadhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxdoesn't look quite right to me.It looks to me like you are missing a relationship between t he two tables.maybe the PO_Number exists between the two table,s and you need to update WHERE PO_NUMBER = INSERTED.PO_Number AND Transaction_Type = 'Bulk Purchase' ?Mon, 14 Jan 2013 10:46:57 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1406837-391-1.aspxI'm trying to write a trigger that will update one table [tblTransactions] when a record is updated in another [tblBulkPurchases].... am I on the right track here? USE [TrackIT]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trUpdateBulkPurchases]ON [dbo].[tblTransactions]AFTER UPDATEAS BEGINUPDATE tblBulkPurchases(tblBulkPurchases.PO_Number, tblBulkPurchases.Quantity, tblBulkPurchases.Transaction_Number, tblBulkPurchases.Quantity_Remaining, tblBulkPurchases.Unit_Price, tblBulkPurchases.Software_Description, tblBulkPurchases.PO_Date, tblBulkPurchases.PurchaseCostCenter, tblBulkPurchases.HeatTicketNumber, tblBulkPurchases.PurchaseAccount, tblBulkPurchases.Transaction_Date, tblBulkPurchases.Transaction_Type, tblBulkPurchases.SoftwareShortName)SELECT INSERTED.PO_Number, INSERTED.Quantity, INSERTED.Transaction_Number, INSERTED.Quantity, INSERTED.Unit_Price, INSERTED.Software_Description, INSERTED.PO_Date, INSERTED.PurchaseCostCenter, INSERTED.HeatTicketNumber, INSERTED.PurchaseAccount, INSERTED.Transaction_Date, INSERTED.Transaction_Type, INSERTED.SoftwareShortName FROM INSERTEDWHERE Transaction_Type = 'Bulk Purchase'ENDMon, 14 Jan 2013 10:29:33 GMTbriancampbellmcad