SQL SERVER 触发器汇总数据使用方法

一、数据准备

创建数据库
创建表

create table myta(id int,price int,name VARCHAR(20))
insert into myta values(1,2,'001')
insert into myta values(2,5,'002')
insert into myta values(3,5,'003')
insert into myta values(4,5,'004')
insert into myta values(5,6,'005')
insert into myta values(6,8,'005')
insert into myta values(7,8,'006')

二、具体案例

场景：在数据库表插入数据时,根据插入的数据为条件汇总原表和当前插入数据的金额。

触发器处理方法：

方案一：当金额大于一定程度（10）时，触发器提醒，没有向底表写入数据
触发器代码：

USE [mytest]
GO
/****** Object:  Trigger [dbo].[test_insert_trigger]    Script Date: 12/08/2023 15:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[test_insert_trigger] ON [dbo].[myta]
FOR INSERT
AS
/* 定义局部变量*/
DECLARE @count float(9),
        @ID  int,
        @price float(9)
        
select @ID  = id from inserted 
select @price = price from inserted 

SELECT @count = SUM(myta.price) 
FROM myta , inserted 
where myta.name = inserted.name
group by myta.name
IF @count <= 10
PRINT('记录插入成功')
ELSE
BEGIN
PRINT('金额大于10!')
ROLLBACK TRANSACTION
--update myta set price = 10 where id = @ID
END

1.金额小于10时成功插入

insert into myta values(8,4,'002')

运行结果提示:

数据库结果:

2.金额大于10时，提示大于10，没有向数据库插入数据

insert into myta values(9,3,'002')

运行结果提示

数据库结果:

方案二：当金额大于一定程度（10）时，触发器提醒，并向底表写入数据
触发器代码：

USE [mytest]
GO
/****** Object:  Trigger [dbo].[test_insert_trigger]    Script Date: 12/08/2023 15:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[test_insert_trigger] ON [dbo].[myta]
FOR INSERT
AS
/* 定义局部变量*/
DECLARE @count float(9),
        @ID  int,
        @price float(9)
        
select @ID  = id from inserted 
select @price = price from inserted 

SELECT @count = SUM(myta.price) 
FROM myta , inserted 
where myta.name = inserted.name
group by myta.name
IF @count <= 10
PRINT('记录插入成功')
ELSE
BEGIN
PRINT('金额大于10!')
--ROLLBACK TRANSACTION
--update myta set price = 10 where id = @ID
END

1.金额小于10时成功插入

insert into myta values(8,7,'001')

运行结果提示

数据库结果:

2.金额大于10时，提示大于10，并且向底表插入数据

insert into myta values(8,2,'001')

运行结果提示

数据库结果:

方案三：当金额大于一定程度（10）时，触发器提醒，并根据插入的其中的一条数据修改价格数（需要从前端传到数据库）
触发器代码：

USE [mytest]
GO
/****** Object:  Trigger [dbo].[test_insert_trigger]    Script Date: 12/08/2023 15:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[test_insert_trigger] ON [dbo].[myta]
FOR INSERT
AS
/* 定义局部变量*/
DECLARE @count float(9),
        @ID  int,
        @price float(9)
        
select @ID  = id from inserted 
select @price = price from inserted 

SELECT @count = SUM(myta.price) 
FROM myta , inserted 
where myta.name = inserted.name
group by myta.name
IF @count <= 10
PRINT('记录插入成功')
ELSE
BEGIN
PRINT('金额大于10!')
--ROLLBACK TRANSACTION
update myta set price = 10 where id = @ID
END

1.金额小于10时成功插入

insert into myta values(8,1,'006')

运行结果提示

数据库结果:

2. 金额大于10时，提示大于10，并且向底表插入数据修改价格

insert into myta values(9,8,'006')

运行结果提示

数据库结果: