CODEWITHSUNDEEP
sql-servertriggerssql-updatessms
JuMoGar
JuMoGar

Reputation: 1760

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

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

Upvotes: 2

Views: 931

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 76817

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

Upvotes: 1

KeithL
KeithL

Reputation: 5594

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)

Upvotes: 0

Related Questions