Solved

MS SQL Server - Table Triggers

Posted on 2014-09-16
8
180 Views
Last Modified: 2014-09-16
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
Comment
Question by:BoltonWanderer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324839
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
 

Author Comment

by:BoltonWanderer
ID: 40324853
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324860
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:BoltonWanderer
ID: 40324862
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40324866
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
 

Author Comment

by:BoltonWanderer
ID: 40324868
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40324879
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
 

Author Comment

by:BoltonWanderer
ID: 40324885
Perfect. Thank you for your help!!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question