Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

MS SQL Server - Table Triggers

Hi,

We are using MS SQL Server 2008 R2 Standard and I am trying to archive a trigger such as when a record is being updated in an application, it will then fire a trigger (in SQL) and update one column for that one row.

Currently I have this working but I am not sure how to use the WHERE statement to only get that record? It currently updates all rows which is what I don't want it to do.

ALTER Trigger [dbo].[UpdateSpareDate21] on [dbo].[StockItem] for INSERT, DELETE as Update StockItem Set SpareDate1 = GetDate()  

Open in new window


I need to put a WHERE clause on this to get the row which has just been updated.

Any ideas?

Thanks,
Alex
0
BoltonWanderer
Asked:
BoltonWanderer
  • 4
  • 4
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
First of all, there is a problem with the Trigger that you currently have.

It says "ALTER Trigger ... for INSERT, DELETE as" - you said you wanted a trigger "when a record is being updated in an application". If it is being deleted, that is not being updated. Plus, you seem to be updating a column in the StockItem table for the row that is being changed. However, if it is being deleted, then there is nothing to update! So I would change that to "ALTER Trigger ... for INSERT, UPDATE as"

The Where clause needs to refer to the inserted. table. You need to have a unique identify. Let's assume that there is a IDStockItem, which is an identity column. Then the where clause becomes something like:

Where inserted.IDStockItem = StockItem.IDStockItem

Without the actual table, I cannot test the solution, because I believe that's the answer.
0
 
BoltonWandererAuthor Commented:
Hi Phillip,

Thanks for the response.

ALTER Trigger [dbo].[UpdateSpareDate21] on [dbo].[StockItem] for update, INSERT  as 
BEGIN
	Update StockItem Set SpareDate1 = GetDate()
END 

Open in new window

I have updated the trigger ( I also need inserts so I have included that).

The primary Key of the table is ItemID

So would this code work

ALTER Trigger [dbo].[UpdateSpareDate21] on [dbo].[StockItem] for update, INSERT  as BEGIN
Declare @ItemID int
	Update StockItem Set SpareDate1 = GetDate() where ItemID = @ItemID
END

Open in new window


I don't have another table to log what is being inserted? Is this needed?

Thanks
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
When you use an Alter Trigger, then Inserted table is automatically generated. Have a read of http://msdn.microsoft.com/en-gb/library/ms191300.aspx for more information.

Update StockItem Set SpareDate1 = GetDate() where StockItem.ItemID = inserted.ItemID

should work (fingers crossed).

Give it a try!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BoltonWandererAuthor Commented:
I will have a read of that thanks.

I sadly am getting an error when I am trying to alter the trigger.

Msg 4104, Level 16, State 1, Procedure UpdateSpareDate21, Line 11
The multi-part identifier "inserted.ItemID" could not be bound.

I don't have another table called Inserted - is this required?
Thanks,
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you please post the current Trigger, and can you post the format of the current table (right-hand click on the table and say Script Table As - Create To - New Query Editor Window).
0
 
BoltonWandererAuthor Commented:
Yes of course.

Trigger is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[UpdateSpareDate21] on [dbo].[StockItem] for update, INSERT  as 
BEGIN
	Update StockItem Set SpareDate1 = GetDate() where StockItem.ItemID = Inserted.ItemID
END 

Open in new window

(This wont Save though so it currently doesn't have the WHERE Clause included)

The table is here

CREATE TABLE [dbo].[StockItem](
	[ItemID] [bigint] NOT NULL,
	[Code] [dbo].[StockCode] NOT NULL,
	[Name] [varchar](60) NOT NULL CONSTRAINT [DF_StockItem_Name]  DEFAULT (''),
	[ProductGroupID] [bigint] NOT NULL,
	[Description] [text] NOT NULL CONSTRAINT [DF_StockItem_Description]  DEFAULT (''),
	[UseDescriptionOnDocs] [bit] NOT NULL CONSTRAINT [DF_StockItem_UseDescriptionOnDocs]  DEFAULT (0),
	[TaxCodeID] [bigint] NOT NULL,
	[StandardCost] [dbo].[MonetaryValue] NOT NULL CONSTRAINT [DF_StockItem_StandardCost]  DEFAULT ((0)),
	[SOPItemPrice] [dbo].[MonetaryValue] NOT NULL CONSTRAINT [DF_StockItem_SOPItemPrice]  DEFAULT ((0)),
	[StockItemStatusID] [bigint] NULL,
	[InactiveDate] [datetime] NULL,
	[Manufacturer] [varchar](40) NOT NULL CONSTRAINT [DF_StockItem_Manufacturer]  DEFAULT (''),
	[PartNumber] [varchar](40) NOT NULL CONSTRAINT [DF_StockItem_PartNumber]  DEFAULT (''),
	[StocktakeCyclePeriod] [smallint] NOT NULL CONSTRAINT [DF_StockItem_StocktakeCyclePeriod]  DEFAULT ((0)),
	[CommodityCode] [varchar](8) NOT NULL CONSTRAINT [DF_StockItem_CommodityCode]  DEFAULT (''),
	[Weight] [dbo].[Mass] NOT NULL CONSTRAINT [DF_StockItem_Weight]  DEFAULT ((0)),
	[SuppressNetMass] [bit] NOT NULL,
	[StockUnitName] [dbo].[ShortName] NOT NULL,
	[BaseUnitName] [dbo].[ShortName] NOT NULL,
	[StockMultOfBaseUnit] [dbo].[Ratio] NOT NULL CONSTRAINT [DF_StockItem_StockMultOfBaseUnit]  DEFAULT ((1)),
	[Barcode] [varchar](30) NOT NULL,
	[StdCostVarianceLastReset] [datetime] NULL,
	[AverageBuyingPrice] [dbo].[MonetaryValue] NOT NULL CONSTRAINT [DF_StockItem_AverageBuyingPrice]  DEFAULT ((0)),
	[TraceableTypeID] [bigint] NOT NULL,
	[SaleFromSingleBatch] [bit] NOT NULL,
	[AllowDuplicateNumbers] [bit] NOT NULL,
	[UsesAlternativeRef] [bit] NOT NULL,
	[UsesSellByDate] [bit] NOT NULL,
	[UsesUseByDate] [bit] NOT NULL,
	[RecordNosOnGoodsReceived] [bit] NOT NULL,
	[LastArchivedUpTo] [datetime] NULL,
	[FreeStockQuantity] [dbo].[StockBalance] NOT NULL CONSTRAINT [DF_StockItem_FreeStockQuantity]  DEFAULT ((0)),
	[BOMItemTypeID] [bigint] NULL CONSTRAINT [DF_StockItem_BOMItemTypeID]  DEFAULT ((0)),
	[SOPOrderFulfilmentMethodID] [bigint] NOT NULL CONSTRAINT [DF_StockItem_SOPOrderFulfilmentMethodID]  DEFAULT ((0)),
	[DefaultDespatchNoteComment] [varchar](160) NOT NULL CONSTRAINT [DF_StockItem_DefaultDespatchNoteComment]  DEFAULT (''),
	[DefaultPickingListComment] [varchar](160) NOT NULL CONSTRAINT [DF_StockItem_DefaultPickingListComment]  DEFAULT (''),
	[QuantityReserved] [dbo].[StockQuantity] NOT NULL CONSTRAINT [DF_StockItem_QuantityReserved]  DEFAULT ((0)),
	[OpLock] [timestamp] NOT NULL,
	[DateTimeCreated] [datetime] NOT NULL CONSTRAINT [DF__StockItem__DateT__6662976A]  DEFAULT (getdate()),
	[LastTraceArchivedUpTo] [datetime] NULL,
	[LandedCostsTypeID] [bigint] NOT NULL DEFAULT (0),
	[LandedCostsValue] [dbo].[MonetaryValue] NOT NULL CONSTRAINT [DF_StockItem_LandedCostsValue]  DEFAULT ((0)),
	[SpareText1] [varchar](100) NOT NULL CONSTRAINT [DF_StockItem_SpareText1]  DEFAULT (''),
	[SpareText2] [varchar](100) NOT NULL CONSTRAINT [DF_StockItem_SpareText2]  DEFAULT (''),
	[SpareText3] [varchar](100) NOT NULL CONSTRAINT [DF_StockItem_SpareText3]  DEFAULT (''),
	[SpareNumber1] [dbo].[MonetaryBalance] NOT NULL CONSTRAINT [DF_StockItem_SpareNumber1]  DEFAULT ((0)),
	[SpareNumber2] [dbo].[MonetaryBalance] NOT NULL CONSTRAINT [DF_StockItem_SpareNumber2]  DEFAULT ((0)),
	[SpareNumber3] [dbo].[MonetaryBalance] NOT NULL CONSTRAINT [DF_StockItem_SpareNumber3]  DEFAULT ((0)),
	[SpareDate1] [datetime] NULL,
	[SpareDate2] [datetime] NULL,
	[SpareDate3] [datetime] NULL,
	[SpareBit1] [bit] NOT NULL CONSTRAINT [DF_StockItem_SpareBit1]  DEFAULT ((0)),
	[SpareBit2] [bit] NOT NULL CONSTRAINT [DF_StockItem_SpareBit2]  DEFAULT ((0)),
	[SpareBit3] [bit] NOT NULL CONSTRAINT [DF_StockItem_SpareBit3]  DEFAULT ((0)),
	[AllowSalesOrder] [bit] NOT NULL CONSTRAINT [DF_StockItem_AllowSalesOrder]  DEFAULT ((1)),
	[STKAutoGenerateOptionTypeID] [bigint] NOT NULL CONSTRAINT [DF_StockItem_STKAutoGenerateOptionTypeID]  DEFAULT ((0)),
	[AutoGeneratePrefix] [varchar](20) NOT NULL,
	[AutoGenerateNextNumber] [bigint] NOT NULL CONSTRAINT [DF_StockItem_AutoGenerateNextNumber]  DEFAULT ((0)),
	[STKLabelPrintingOptionTypeID] [bigint] NOT NULL CONSTRAINT [DF_StockItem_STKLabelPrintingOptionTypeID]  DEFAULT ((0)),
	[STKFulfilmentSequenceTypeID] [bigint] NOT NULL CONSTRAINT [DF_StockItem_STKFulfilmentSequenceTypeID]  DEFAULT ((0)),
	[ShelfLife] [int] NOT NULL CONSTRAINT [DF_StockItem_ShelfLife]  DEFAULT ((0)),
	[STKShelfLifeTypeID] [bigint] NULL,
	[AllowOutOfDate] [bit] NOT NULL CONSTRAINT [DF_StockItem_AllowOutOfDate]  DEFAULT ((0)),
	[STKAutoGenerateSeparatorID] [bigint] NOT NULL CONSTRAINT [DF_StockItem_STKAutoGenerateSeparatorID]  DEFAULT ((1)),
	[AutoGeneratePadding] [int] NOT NULL CONSTRAINT [DF_StockItem_AutoGeneratePadding]  DEFAULT ((0)),
	[CountryOfOriginID] [bigint] NULL,
	[UsesSupplementaryUnit] [bit] NOT NULL CONSTRAINT [DF_StockItem_UsesSupplementaryUnit]  DEFAULT ((0)),
	[SupplementaryUnitConversionRatio] [dbo].[Ratio] NOT NULL CONSTRAINT [DF_StockItem_SupplementaryUnitConversionRatio]  DEFAULT ((1)),
	[UsesRCV] [bit] NOT NULL CONSTRAINT [DF_StockItem_UsesRCV]  DEFAULT ((0)),
	[IsWEEEItem] [bit] NOT NULL CONSTRAINT [DF_StockItem_IsWEEEItem]  DEFAULT ((0)),
	[IncludeNosOnCountSheets] [bit] NOT NULL CONSTRAINT [DF_StockItem_IncludeNosOnCountSheets]  DEFAULT ((1)),
 CONSTRAINT [PK_StockItem] PRIMARY KEY CLUSTERED 
(
	[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_BOMItemType] FOREIGN KEY([BOMItemTypeID])
REFERENCES [dbo].[BOMItemType] ([BOMItemTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_BOMItemType]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_LandedCostsType] FOREIGN KEY([LandedCostsTypeID])
REFERENCES [dbo].[LandedCostsType] ([LandedCostsTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_LandedCostsType]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_ProductGroup] FOREIGN KEY([ProductGroupID])
REFERENCES [dbo].[ProductGroup] ([ProductGroupID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_ProductGroup]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_ShelfLifeTypeID] FOREIGN KEY([STKShelfLifeTypeID])
REFERENCES [dbo].[STKShelfLifeType] ([STKShelfLifeTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_ShelfLifeTypeID]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_SOPOrderFulfilmentMethod] FOREIGN KEY([SOPOrderFulfilmentMethodID])
REFERENCES [dbo].[SOPOrderFulfilmentMethod] ([SOPOrderFulfilmentMethodID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_SOPOrderFulfilmentMethod]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_STKAutoGenerateOptionTypeID] FOREIGN KEY([STKAutoGenerateOptionTypeID])
REFERENCES [dbo].[STKAutoGenerateOptionType] ([STKAutoGenerateOptionTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_STKAutoGenerateOptionTypeID]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_STKAutoGenerateSeparator] FOREIGN KEY([STKAutoGenerateSeparatorID])
REFERENCES [dbo].[STKAutoGenerateSeparator] ([STKAutoGenerateSeparatorID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_STKAutoGenerateSeparator]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_STKFulfilmentSequenceTypeID] FOREIGN KEY([STKFulfilmentSequenceTypeID])
REFERENCES [dbo].[STKFulfilmentSequenceType] ([STKFulfilmentSequenceTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_STKFulfilmentSequenceTypeID]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_STKLabelPrintingOptionTypeID] FOREIGN KEY([STKLabelPrintingOptionTypeID])
REFERENCES [dbo].[STKLabelPrintingOptionType] ([STKLabelPrintingOptionTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_STKLabelPrintingOptionTypeID]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_StockItemStatus] FOREIGN KEY([StockItemStatusID])
REFERENCES [dbo].[StockItemStatus] ([StockItemStatusID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_StockItemStatus]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_SYSCountryCode] FOREIGN KEY([CountryOfOriginID])
REFERENCES [dbo].[SYSCountryCode] ([SYSCountryCodeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_SYSCountryCode]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_SYSTaxRate] FOREIGN KEY([TaxCodeID])
REFERENCES [dbo].[SYSTaxRate] ([SYSTaxRateID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_SYSTaxRate]
GO

ALTER TABLE [dbo].[StockItem]  WITH CHECK ADD  CONSTRAINT [FK_StockItem_TraceableType] FOREIGN KEY([TraceableTypeID])
REFERENCES [dbo].[TraceableType] ([TraceableTypeID])
GO

ALTER TABLE [dbo].[StockItem] CHECK CONSTRAINT [FK_StockItem_TraceableType]
GO

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Got it - I was referencing the wrong table. Here's the solution:

alter Trigger [dbo].[UpdateSpareDate21] on [dbo].[StockItem] after update, insert as
BEGIN
      Update StockItem
      Set SpareDate1 = GetDate()
      FROM inserted
      where inserted.itemID = StockItem.ItemID
END
0
 
BoltonWandererAuthor Commented:
Perfect. Thank you for your help!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now