Update a column when price column of other table is modified. Triggers. Nested tables. SQL Server

Question

In my database I have about 10 tables connected in one central table (Mobile). This table (Mobile) has a column called price which is the sum of the prices of all other nested tables. I would like that when price of another table (like Battery, Camera, ...) is updated, the price of the central table (Mobile) would be updated too.

I will show the schema of central table and two more (for reducing code, other nested tables are so similar)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table_mobile]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [processor] [int] NOT NULL,
    [memory_ram] [int] NOT NULL,
    [memory_rom] [int] NOT NULL,
    [operating_system] [int] NOT NULL,
    [graphic] [int] NOT NULL,
    [screen] [int] NOT NULL,
    [battery] [int] NOT NULL,
    [camera] [int] NOT NULL,
    [material] [int] NOT NULL,
    [extra] [int] NOT NULL,
    [price] [decimal](18, 2) NOT NULL,
    [created_by] [int] NOT NULL,
    [created_at] [timestamp] NOT NULL,

    CONSTRAINT [PK_mobiles] 
        PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table_battery]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [capacity] [int] NOT NULL,
    [description] [varchar](250) NOT NULL,
    [image] [image] NOT NULL,
    [price] [decimal](18, 2) NOT NULL,

    CONSTRAINT [PK_table_battery] 
        PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table_camera]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [megapixels] [int] NOT NULL,
    [description] [varchar](250) NOT NULL,
    [image] [image] NOT NULL,
    [price] [decimal](18, 2) NOT NULL,

    CONSTRAINT [PK_table_camera] 
        PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

As I say, I think that my purpose should be achieve with a trigger but any other suggest is accepted.

I'll show you what I want to do by programming in C#:

table_mobile.price = table_battery.price + table_camera.price + ... + table_XXX.price

Any idea how can I achive my trouble?

Thank you.

EDIT 1:

Using SSMS... I have created this template for a Trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,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

END
GO

I would roll up the summary pricing in a view and not use a trigger. — KeithL, May 21 '18 at 17:21
Agreed, triggers are always a last resort for me. I would avoid storing the price in the mobile table at all. — DavidG, May 21 '18 at 17:22
Ok, thank you both. I also think not including this column, but it is a requisite so... I must do it — JuMoGar, May 21 '18 at 17:50
@KeithL can you answer with the view example? I have never done one. As I say in the question, trigger is not imprescindible, other ways are accepted too :) — JuMoGar, May 21 '18 at 17:51
@JuMoGar I gave you the view in the answers. You just join the detail tables and add the price for total price. — KeithL, May 21 '18 at 18:02

Lajos Arpad · Accepted Answer · 2018-05-21T19:57:26.197

1

I have not worked with SQL Server for a while, so forgive me if there are any typos, but basically you will need to create a trigger for each of the tables linked to mobile and add the difference of the new and the old value to the price of the mobile:

create trigger PriceChange on table_battery
after update
as
BEGIN
update table_mobile
set price = table_mobile.price + i.price
from table_mobile
inner join INSERTED i
on table.mobile.id = i.id;

update table_mobile
set price = table_mobile.price - d.price
from table_mobile
inner join DELETED d
on table.mobile.id = d.id;
END

Note that we do separate updates, because the id might have changed. If the id stays the same, then you can use a single update with a difference. The code is untested, so if there are any problems, then please, let me know.

EDIT

You may also do this from application level where you trigger the updates. After any such update you can run an update for table_mobile, adding the values. The benefit would be that you can do the calculation only once if you know that several prices for the same mobiles will be changed.

EDIT2

Apparently this command should be used inside the trigger:

UPDATE [dbo].[table_mobile] 
SET price = table_mobile.price + i.price - d.price 
FROM [dbo].[table_mobile], 
INSERTED i, 
DELETED d 
WHERE battery = d.id

edited May 21 '18 at 19:57

answered May 21 '18 at 17:20

Lajos Arpad

64,414
37
100
175

Thank you! I have tryed it. It works but only changes first mobile. Not the others wich also contains changed element. Why could it be? – JuMoGar May 21 '18 at 17:44
Also add that ID not changed. It is PK autoincremental(1) so always is the same – JuMoGar May 21 '18 at 17:53
@JuMoGar ok, this means that you can do this using a single update joining to both inserted and deleted and set price = table_mobile.price + i.price - d.price. You have also stated that this works on the first mobile. Can you elaborate on how you are doing the updates, what is the expected result and what do you get instead? The current description gives me a vague idea, but we need to know the exact problem. – Lajos Arpad May 21 '18 at 18:00
I have achieve it doing next: `UPDATE [dbo].[table_mobile] SET price = table_mobile.price + i.price - d.price FROM [dbo].[table_mobile], INSERTED i, DELETED d` – JuMoGar May 21 '18 at 19:06
But the problem of that is: change any row, price will updated in each mobile, independent of it is its id – JuMoGar May 21 '18 at 19:21
so... all joined: `UPDATE [dbo].[table_mobile] SET price = table_mobile.price + i.price - d.price FROM [dbo].[table_mobile], INSERTED i, DELETED d WHERE battery = d.id`. Update your answer and I will select it. Thank you – JuMoGar May 21 '18 at 19:50
@JuMoGar thank you, I have edited my answer to reflect your findings. – Lajos Arpad May 21 '18 at 19:57

KeithL · Answer 2 · 2018-05-21T18:13:33.027

0

This is an example of the view I mentioned:

create view MobileWithPriceAggregate as
select  [id]
,   [name]
,   [processor]
,   [memory_ram]
,   [memory_rom]
,   [operating_system]
,   [graphic]
,   [screen]
,   [battery]
,   [camera]
,   [material]
,   [extra]
, price = m.price+b.price+c.price
from [table_mobile] m
    join [table_battery] b on b.id=m.battery
    join [table_camera] c on c.id=m.camera

Note: if not all Mobiles have a camera, then you need to use left join and null handle like ISNULL(c.price,0)

edited May 21 '18 at 18:13

answered May 21 '18 at 17:58

KeithL

5,348
3
19
25

In the last part of your answer, Should not it be... `price = m.price+b.price+c.price` and `join [table_camera] c on c.id=m.camera`? – JuMoGar May 21 '18 at 18:12
Also, this script will update `mobile.price` after each XXX.price updates? (beeing XXX = camera, battery,...) – JuMoGar May 21 '18 at 18:13
It will calculate every time. So whatever the current sub-prices are will be used in the calculation. I wasn't sure if the mobile had it's own price or not or if was just a sum of it's components. Just adjust by adding in m.price or not – KeithL May 21 '18 at 18:15
I updated the join, it's easy to make those mistakes when you are answering directly in the browser. – KeithL May 21 '18 at 18:17
Ok, thank you for your, I am going to andjust and try this and say you if all is Ok. If it is not so much trouble... Why is better the use of a View instead of a Trigger? – JuMoGar May 21 '18 at 18:20
1. The calculation is straightforward and not hidden in a trigger for someone to have to really search for. 2. You are creating however many transactions that the affected price is connected to. 3. It's a lot easier to edit a view than a trigger as it is a straight forward select in this case. – KeithL May 21 '18 at 18:26
Ok, thank you again. I have tryed it but does not work, I do not know why – JuMoGar May 21 '18 at 19:06

Update a column when price column of other table is modified. Triggers. Nested tables. SQL Server

2 Answers2