Solved

MS SQL Server - Table Triggers

Posted on 2014-09-16
8
165 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
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:BoltonWanderer
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect. Thank you for your help!!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now