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
BoltonWandererAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BoltonWandererAuthor Commented:
Perfect. Thank you for your help!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.