需要这样一个记录本-2 SQL Server触发器应用实例

本文链接：https://blog.youkuaiyun.com/juns6/article/details/39369237

利用SQL触发器，完成项目统计功能。加快程序查看报表的速度。

首先建立基础表：TB_IOModuleInput

/*******直接复制到SQL中查询分析器***********/

CREATE TABLE [dbo].[TB_IOModuleInput](
[FID] [int] IDENTITY(1,1) NOT NULL,
[FIOName] [varchar](32) NULL,
[FRecordDT] [datetime] NULL
) ON [PRIMARY]

建立统计结果表：TB_IOModule_Month_RP

/********按月统计IOName输入输出次数**********************/

/********年统计字段FTotal，利用SQL中‘计算所得的列规范’自动进行统计**********************/

CREATE TABLE [dbo].[TB_IOModule_Month_RP](
[FID] [int] IDENTITY(1,1) NOT NULL,
[FIOName] [nvarchar](32) NOT NULL,
[FJanurary] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FJanurary] DEFAULT ((0)),
[FFebruary] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FFebruary] DEFAULT ((0)),
[FMarch] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FMarch] DEFAULT ((0)),
[FApril] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FApril] DEFAULT ((0)),
[FMay] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FMay] DEFAULT ((0)),
[FJune] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FJune] DEFAULT ((0)),
[FJuly] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FJuly] DEFAULT ((0)),
[FAugust] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FAugust] DEFAULT ((0)),
[FSeptember] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FSeptember] DEFAULT ((0)),
[FOctober] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FOctober] DEFAULT ((0)),
[FNovember] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FNovember] DEFAULT ((0)),
[FDecember] [int] NULL CONSTRAINT [DF_TB_Month_RP_IOName_FDecember] DEFAULT ((0)),
[FTotal] AS ((((((((((([FJanurary]+[FFebruary])+[FMarch])+[FApril])+[FMay])+[FJune])+[FJuly])+[FAugust])+[FSeptember])+[FOctober])+[FNovember])+[FDecember]) PERSISTED
) ON [PRIMARY]

对表TB_IOModuleInput建立Insert触发器：tg_IOModuleInput_Insert_MonthRP

/***************当TB_IOModuleInput表有新增加记录时，修改统计表TB_IOModule_Month_RP中对应记录***********/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tg_IOModuleInput_Insert_MonthRP]
ON [dbo].[TB_IOModuleInput]
AFTER INSERT
AS
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MONTHINT int
DECLARE @IONAME VARCHAR(32)
select @MONTHINT = Datename(month, (select top(1) FRecordDT from INSERTED))
SELECT TOP(1) @IONAME = FIONAME FROM INSERTED
if (@MONTHINT = 1)
BEGIN
update TB_Month_RP_IOName set FJanurary = FJanurary + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 2)
BEGIN
UPDATE TB_Month_RP_IOName set FFebruary = FFebruary + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 3)
BEGIN
UPDATE TB_Month_RP_IOName set FMarch = FMarch + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 4)
BEGIN
UPDATE TB_Month_RP_IOName set FApril = FApril + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 5)
BEGIN
UPDATE TB_Month_RP_IOName set FMay = FMay + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 6)
BEGIN
UPDATE TB_Month_RP_IOName set FJune = FJune + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 7)
BEGIN
UPDATE TB_Month_RP_IOName set FJuly = FJuly + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 8)
BEGIN
UPDATE TB_Month_RP_IOName set FAugust = FAugust + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 9)
BEGIN
UPDATE TB_Month_RP_IOName set FSeptember = FSeptember + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 10)
BEGIN
UPDATE TB_Month_RP_IOName set FOctober = FOctober + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 11)
BEGIN
UPDATE TB_Month_RP_IOName set FNovember = FNovember + 1 WHERE FIONAME = @IONAME
END
ELSE IF (@MONTHINT = 12)
BEGIN
UPDATE TB_Month_RP_IOName set FDecember = FDecember + 1 WHERE FIONAME = @IONAME
END
ELSE
BEGIN
ROLLBACK
RETURN
END
-- Insert statements for trigger here
select 1
COMMIT TRANSACTION