December 18, 2009 at 11:26 am
Any help will be greatly appreciated.
I have Three tables :
Note: I have oversimplified the table schema for convenience.
VersionMaster
1. Version ID (PK)
2. CostTotal
VersionCostDetail
1. Tno(PK)
2. VersionID(FK from VersionMaster)
3. CostId(FK from CostMaster)
4. Amount
Trigger
1. OnVersionCostChange
CostMaster
1. CostId(PK)
2. Amount
Triggers:
1. OnCostChange
Scenario:
When i change the amount in the Cost Master table a trigger(OnCostChange) updates the amount in the VersionCostDetail for the specified CostId. Another(OnVersionCostChange) trigger fires and updates the Version tables CostTotal for the specific version. This scenario work great if there is only one version effected by the cost change. If there are multiple versions affected by the cost change then i get familiar error:
"subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
I am aware that i need to handle this by joining insert/delete conceptual tables available to triggers. But i am not able to get my head around it. If anyone can help me solve this issue it would be great.
Environment: MS SQL SERVER 05
December 18, 2009 at 11:38 am
Help us help you, please read and follow the iinstructions in the first article I reference below in my signature block regarding asking for assistance. Also, based on the sample data you provide, please provide the expected results when all is done. You should also provide the code you have currently developed.
What you get in return for your extra effort includes better answers to your questions and tested code.
December 18, 2009 at 11:44 am
Why bother denormalizing it this way? Why not just query aggregates? It looks to me like you could get rid of the VersionMaster table completely, or replace it with an indexed view.
If you really need to denormalize it, here's what I'd probably write based on the table descriptions provided:
create trigger CostMaster_Update on dbo.CostMaster
after update
as
set nocount on;
update VCD
set Amount = inserted.Amount
from dbo.VersionCostDetail VCD
inner join inserted
on VCD.CostID = inserted.CostID
and VCD.Amount != inserted.Amount;
go
create trigger VersionCostDetail_Update on dbo.VersionCostDetail
after update
as
set nocount on;
update VM
set CostTotal =
(select sum(Amount)
from dbo.VersionCostDetail
where VersionID = VM.VersionID
and VersionID in
(select inserted.VersionID
from inserted
inner join deleted
on inserted.Tno = deleted.Tno
and inserted.Amount != deleted.Amount))
from dbo.VersionMaster VM;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2009 at 11:45 am
if you are getting a specific error, we really need to see the code in your triggers; that's where the error lies; it sounds like you have declared variables in your triggers, which probably fail every time a multi-row update occurs.
show us the details so we can help.
Lowell
December 18, 2009 at 12:39 pm
Thank You for help everybody.
Hope this helps .
Tables:
1. Cost Table (All costs defined here )
2. Version(All versions defined here)
3. VersionCost(cost detail for a version)
4. VersionCostHistory (Tracks whatever is added/deleted and updated to a Version)
Any cost changes in the cost table need to update the CostTotal of all versions affected by a change. The problem is that one costId can be used by multiple versions and all affected versions need to be updated and then the updated version Cost total needs to be written to VersionCost History table.
1. I am using a trigger to update amount in the VersionCost table when a cost update takes place in the cost table.
2. I want to use another trigger to update CostTotal of the Version table when amount in the VersionCost table changes for a version. In case of multiple versions being effected all versions CostTotal in Version table needs to be updated.
I hope this clears it up.
Cost Table:
CREATE TABLE [dbo].[Cost](
[CostId] [int] IDENTITY(1,1) NOT NULL,
[FactoryId] [int] NULL,
[CategoryId] [int] NULL,
[Cost] [varchar](250) NULL,
[Amount] [money] NULL,
[Notes] [varchar](max) NULL,
[UserName] [varchar](50) NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_Cost_DateAdded] DEFAULT (getdate()),
[Active] [varchar](50) NULL,
CONSTRAINT [PK_Cost] PRIMARY KEY CLUSTERED
(
[CostId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Cost] WITH CHECK ADD CONSTRAINT [FK_Cost_CostCategory] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[CostCategory] ([CategoryId])
GO
ALTER TABLE [dbo].[Cost] CHECK CONSTRAINT [FK_Cost_CostCategory]
GO
ALTER TABLE [dbo].[Cost] WITH CHECK ADD CONSTRAINT [FK_Cost_Factory] FOREIGN KEY([FactoryId])
REFERENCES [dbo].[Factory] ([FactoryId])
GO
ALTER TABLE [dbo].[Cost] CHECK CONSTRAINT [FK_Cost_Factory]
Version Table:
CREATE TABLE [dbo].[Version](
[VersionId] [int] IDENTITY(1,1) NOT NULL,
[Product] [varchar](100) NULL,
[FactoryId] [int] NULL,
[CustomerId] [int] NULL,
[MarketId] [int] NULL,
[VersionTypeId] [int] NULL,
[SpecialOrder] [varchar](50) NULL,
[BaseTotal] [money] NULL CONSTRAINT [DF_Version_BaseTotal] DEFAULT ((0)),
[CostTotal] [money] NULL CONSTRAINT [DF_Version_CostTotal] DEFAULT ((0)),
[PoTotal] [money] NULL CONSTRAINT [DF_Version_PoTotal] DEFAULT ((0)),
[BaseTotalNotes] [varchar](max) NULL,
[VersionNotes] [varchar](max) NULL,
[UserName] [varchar](150) NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_Version_DateAdded] DEFAULT (getdate()),
[Active] [varchar](50) NULL,
CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED
(
[VersionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Version] WITH CHECK ADD CONSTRAINT [FK_Version_Customer] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerId])
GO
ALTER TABLE [dbo].[Version] CHECK CONSTRAINT [FK_Version_Customer]
GO
ALTER TABLE [dbo].[Version] WITH CHECK ADD CONSTRAINT [FK_Version_Factory] FOREIGN KEY([FactoryId])
REFERENCES [dbo].[Factory] ([FactoryId])
GO
ALTER TABLE [dbo].[Version] CHECK CONSTRAINT [FK_Version_Factory]
GO
ALTER TABLE [dbo].[Version] WITH CHECK ADD CONSTRAINT [FK_Version_Market] FOREIGN KEY([MarketId])
REFERENCES [dbo].[Market] ([MarketId])
GO
ALTER TABLE [dbo].[Version] CHECK CONSTRAINT [FK_Version_Market]
GO
ALTER TABLE [dbo].[Version] WITH CHECK ADD CONSTRAINT [FK_Version_VersionType] FOREIGN KEY([VersionTypeId])
REFERENCES [dbo].[VersionType] ([VersionTypeId])
GO
ALTER TABLE [dbo].[Version] CHECK CONSTRAINT [FK_Version_VersionType]
VersionCost:
CREATE TABLE [dbo].[VersionCost](
[Tno] [int] IDENTITY(1,1) NOT NULL,
[VersionId] [int] NULL,
[CostId] [int] NULL,
[Amount] [money] NULL,
[Notes] [varchar](max) NULL,
[UserName] [varchar](150) NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_VersionCost_DateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_VersionCost] PRIMARY KEY CLUSTERED
(
[Tno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[VersionCost] WITH CHECK ADD CONSTRAINT [FK_VersionCost_Cost] FOREIGN KEY([CostId])
REFERENCES [dbo].[Cost] ([CostId])
GO
ALTER TABLE [dbo].[VersionCost] CHECK CONSTRAINT [FK_VersionCost_Cost]
GO
ALTER TABLE [dbo].[VersionCost] WITH CHECK ADD CONSTRAINT [FK_VersionCost_Version] FOREIGN KEY([VersionId])
REFERENCES [dbo].[Version] ([VersionId])
GO
ALTER TABLE [dbo].[VersionCost] CHECK CONSTRAINT [FK_VersionCost_Version]
Version Cost History:
CREATE TABLE [dbo].[VersionCostHistory](
[Tno] [int] IDENTITY(1,1) NOT NULL,
[VersionId] [int] NULL,
[Action] [varchar](50) NULL,
[CostId] [int] NULL,
[PoTotal] [money] NULL,
[Notes] [varchar](max) NULL,
[UserName] [varchar](150) NULL,
[DateOfChange] [datetime] NULL,
[Acknowledge] [varchar](50) NULL,
CONSTRAINT [PK_VersionCostHistory] PRIMARY KEY CLUSTERED
(
[Tno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
December 18, 2009 at 12:45 pm
good job posting details Bryan! that gives us just what we need for testing.
with the table definitions, we can emulate what you are doing, and provide solid comprehensive answers.
now we just need the trigger script itself.
Lowell
December 18, 2009 at 12:46 pm
Blind I may be, where are the trigger(s), sample data, expected results after processing?
December 18, 2009 at 12:54 pm
The user interface restricts multiple cost changes. So this trigger works fine .
GO
/****** Object: Trigger [dbo].[OnUpdateVersionCost] Script Date: 12/18/2009 13:51:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[OnUpdateVersionCost]
ON [dbo].[Cost]
AFTER UPDATE
AS
declare @CostId as integer
declare @FactoryId as integer
declare @CategoryId as integer
declare @Cost as varchar(50)
declare @Amount as money
Declare @Notes as varchar(max)
declare @UserName as varchar(100)
BEGIN
set @CostId=(select CostId from inserted)
set @FactoryId=(select FactoryId from inserted)
set @CategoryId=(select CategoryId from inserted)
set @Cost=(select Cost from inserted)
set @Amount=(select Amount from inserted)
set @Notes=(select Notes from inserted)
set @UserName=(select UserName from inserted)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
UPDATE VersionCost SET Amount=@Amount where CostId=@CostId
END
December 18, 2009 at 1:27 pm
Sample data, expected results?? The more you do to help u help you, the better answers you will get.
December 18, 2009 at 3:02 pm
The user interface restricts multiple cost changes. So this trigger works fine
And 6 months from now you can't make changes because somehow 2 rows are updated. Always code triggers to handle sets. Also your original question says you are getting an error and that error is because your code is not handling sets correctly
Here's an article[/url] that explains how and why to write set-based triggers.
Here's an idea on how I'd do this trigger:
ALTER TRIGGER [dbo].[OnUpdateVersionCost]
ON [dbo].[Cost]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
UPDATE VersionCost
SET Amount=I.Amount
FROM
inserted AS I
WHERE
VersionCost.CostId=I.CostId
END
It's simpler, more efficient, and set-based.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply