Solved

MS SQL Server - Table Triggers

Posted on 2014-09-16
8
169 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
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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

23 Experts available now in Live!

Get 1:1 Help Now