|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
|
|
I'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] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[trUpdateBulkPurchases] ON [dbo].[tblTransactions] AFTER UPDATE AS BEGIN 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) 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 INSERTED WHERE Transaction_Type = 'Bulk Purchase' END
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613,
Visits: 27,669
|
|
doesn'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' ?
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
|
|
| Correct... do need to associate by PO = PO... thanks.... is the word INSERTED.____ appropriate to use?
|
|
|
|
|
SSC-Dedicated
           
Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410,
Visits: 13,726
|
|
INSERTED.___ is fine.
I'd do this, but your way works:
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) SELECT i.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'
Follow me on Twitter: @way0utwest
 Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
|
|
Kinda like your way... but the compiler doesn't like where I've placed astericks:
USE [TrackIT] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[trUpdateBulkPurchases] ON [dbo].[tblTransactions] AFTER UPDATE AS BEGIN 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) 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.SoftwareShortName FROM INSERTED i inner join tblBulkPurchases on tblBulkPurchases.Transaction_Number = i.Transaction_Number WHERE Transaction_Type = 'Bulk Purchase' * GO
|
|
|
|
|
SSC-Dedicated
           
Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410,
Visits: 13,726
|
|
Sorry, wasn't reading well. The problem is that you have a malformed UPDATE statement.
CREATE TRIGGER [dbo].[trUpdateBulkPurchases] ON [dbo].[tblTransactions] AFTER UPDATE AS BEGIN UPDATE tblBulkPurchases set PO_Number = i.PO_Number , Quantity = i.quantity ... FROM INSERTED i inner join tblBulkPurchases on tblBulkPurchases.Transaction_Number = i.Transaction_Number WHERE Transaction_Type = 'Bulk Purchase' GO
Follow me on Twitter: @way0utwest
 Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
|
|
Like this? (Still doesn't like near where the * is):
USE [TrackIT] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[trUpdateBulkPurchases] ON [dbo].[tblTransactions] AFTER UPDATE AS BEGIN UPDATE tblBulkPurchases SET PO_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.SoftwareShortName FROM INSERTED i inner join tblBulkPurchases on tblBulkPurchases.Transaction_Number = i.Transaction_Number WHERE Transaction_Type = 'Bulk Purchase' * GO
|
|
|
|
|
SSC-Dedicated
           
Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410,
Visits: 13,726
|
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
|
|
WHERE Transaction_Type = 'Bulk Purchase'
Msg 102, Level 15, State 1, Procedure trUpdateBulkPurchases, Line 21 Incorrect syntax near 'Bulk Purchase'.
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:42 PM
Points: 477,
Visits: 3,649
|
|
briancampbellmcad (1/14/2013) Like this? (Still doesn't like near where the * is): ... WHERE Transaction_Type = 'Bulk Purchase' * GO
Yes, like that but no asterix. You won't be using an * here.
______________________________________________________________________________ How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
|
|
|
|