in the DDL's I have Transaction_Number as the primary unique key, but the PO_Number can be repeated and is not unique:

CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED

([Transaction_Number]

in the blkPurchases table...

Maybe explaining the relationships above would help... the first trigger populates the tblBulkPurchases via entries to the tblTransactions IF it is a 'Bulk Purchase' of a specific product say...

Really I appreciate challenging me as that is the way to learn 🙂 - I read the article and I am trying to apply it. I understand the concept of...

typical INSERT:

INSERT INTO [TrackIT].[dbo].[tblTransactions]

([Buyer_Initial],[PO_Number],[Software_ID],[Transaction_Number],[Transaction_Type])

VALUES

('OCC','5465115','51',17999,'From Bulk Assignment')

GO

the above should trigger a decrement by 1 of the value in [tblBulkPurchases].[Quantity_Remaining]:

UPDATE [TrackIT].[dbo].[tblBulkPurchases]

SET [Quantity_Remaining] = [Quantity_Remaining] - 1

and determine which record in...

"Start by using the INSERTED table (which will have exactly the same columns as the table the

trigger is on) to identify what rows were affected by the insert"

I'm assuming...

and Here's my typical DML statement:

INSERT INTO [TrackIT].[dbo].[tblTransactions]

([Buyer_Initial],[PO_Number],[Software_ID],[Transaction_Number],[Transaction_Type])

VALUES

('OCC','5465115','51',17999,'From...

Here are my DDL of Tables:

USE [TrackIT]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblTransactions](

[PO_Number] [varchar](50) NULL,

[Buyer_Initial] [varchar](50) NULL,

[Software_ID] [varchar](50) NULL,

[Quantity] [int] NULL,

[Unit_Price] [money] NULL,

[Software_Description] [varchar](100) NULL,

[AllocationAccount] [varchar](50) NULL,

[PurchaseAccount] [varchar](50)...

That last part makes sense...

I'm thinking an "IF" condition on transaction_type may be a step in the right direction (?):

USE [TrackIT]

GO

CREATE TRIGGER [dbo].[trDecrementBulkPurchases]

ON [dbo].[tblTransactions]

AFTER INSERT

AS

BEGIN

IF Transaction_Type = 'From Bulk...

Let me make sure I understand this line by line and have each right so far...

First, isn't the line "WHERE Transaction_Type = 'From Bulk Assignment'"

correctly referenceing the INSERTED with...

I read the post but I'm sure I misunderstood something -

Here's a typical insert to the tblTransactions:

INSERT INTO [TrackIT].[dbo].[tblTransactions]

...

Gave this a try to no avail:

USE [TrackIT]

GO

CREATE TRIGGER [dbo].[trDecrementBulkPurchases]

ON [dbo].[tblTransactions]

AFTER INSERT

AS

BEGIN

UPDATE [TrackIT].[dbo].[tblBulkPurchases]

SET [Quantity_Remaining] = [Quantity_Remaining] - 1

WHERE Transaction_Type = 'From Bulk Assignment' AND

[tblBulkPurchases].[PO_Number]...

Now it is working flawlessly by replacing the qualifiers with INSERTED. ... thanks all!

I'm getting the following error for each field of the tblTransactions when I try to execute the T-SQL to create the trigger:

Msg 4104, Level 16, State 1, Procedure trPopulateBulkPurchases, Line...