代码之家 › 专栏 › 技术社区 › JuMoGar

修改其他表的价格列时更新列。触发器。嵌套表。SQL服务器

ssms sql-update triggers sql-server

JuMoGar · 技术社区 · 6 年前

在我的数据库中,有大约10个表连接在一个中心表中( Mobile )这张桌子( 手机 )有一列名为 price 它是所有其他嵌套表的价格之和。我想在什么时候 价格 另一张桌子(比如 Battery , Camera ,…)更新,中央表的价格( 手机 )也会被更新。

我将展示中心表的模式和另外两个(为了减少代码,其他嵌套表非常相似)

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

正如我所说,我认为我的目标应该是 trigger 但任何其他建议都是可以接受的。

我将用C语言编程,向您展示我想做的事情:

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

你知道我怎样才能解决我的麻烦吗?

谢谢您。

编辑1:

使用SSMS…我已经为触发器创建了此模板:

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

2 回复 | 直到 6 年前

Lajos Arpad 6 年前

我有一段时间没有与SQL Server合作过,所以如果有任何错别字,请原谅,但基本上您需要为链接到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

请注意,我们进行了单独的更新,因为ID可能已更改。如果ID保持不变,则可以使用具有差异的单个更新。代码未经测试,如果有任何问题,请告诉我。

编辑

您也可以从触发更新的应用程序级别执行此操作。在任何此类更新之后,您可以运行表移动的更新,添加值。这样做的好处是,如果您知道相同手机的几个价格将发生变化,那么您只能进行一次计算。

编辑2

显然,这个命令应该在触发器内部使用:

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

KeithL 6 年前

这是我提到的视图的一个示例:

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

注意:如果不是所有手机都有摄像头,则需要使用左连接和空句柄,如isNull(c.price,0)