SQL Server 利用触发器对多表视图进行更新的实现方法--Java学习网

时间:2016-11-10 8:13:01

  核心提示:其步骤就是:利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表,然后将对应的数据更新到对应数据表中的字段信息中;.imageplus-appen...

其步骤就是:利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表,然后将对应的数据更新到对应数据表中的字段信息中;



1.首先创建3个表:

a.信息表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE [SQL-LI]
BEGIN TRANSACTION CHUANGJIAN_XINXIN_TAB
--创建命名为【XINXIN_TAB】的数据表,同时不允许字段为空
CREATE TABLE XINXIN_TAB
(
姓名 NVARCHAR(10) NOT NULL,
性别 NVARCHAR(1) NOT NULL,
学号 INT NOT NULL,
班级 NVARCHAR(20) NOT NULL,
出生日期 DATE NOT NULL,
CONSTRAINT XUEHAO_YUESU PRIMARY KEY CLUSTERED
([学号]ASC)
)
COMMIT TRANSACTION CHUANGJIAN_XINXI_TAB
GO

b.明细分数表:

1
2
3
4
5
6
7
8
9
USE [SQL-LI]
CREATE TABLE FENSHU_TAB
(
[学号] INT NOT NULL,
[语文] DECIMAL(3,1) NOT NULL,
[数学] DECIMAL(3,1) NOT NULL,
[英语] DECIMAL(3,1) NOT NULL
)
GO

c.综合分数表:

1
2
3
4
5
6
7
8
USE [SQL-LI]
CREATE TABLE ZHONGHE_TAB
(
[姓名] NVARCHAR(10) NOT NULL,
[学号] INT NOT NULL,
[总分] DECIMAL(4,1) NOT NULL,
[平均分] DECIMAL(3,1) NOT NULL)
GO

2.1.【信息表】和【明细分数表】插入对应表中的数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
USE [SQL-LI]
--插入【XINXIN_TAB】表中的5条记录
INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[学号] ,[性别] ,[班级] ,[出生日期] )
VALUES('李晓峰',6080,'男','计算机','2013-05-03')
 
INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[学号] ,[性别] ,[班级] ,[出生日期] )
VALUES('李晓峰1',6081,'男','计算机1','2013-05-04')
 
INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[学号] ,[性别] ,[班级] ,[出生日期] )
VALUES('李晓峰2',6082,'男','计算机2','2013-05-05')
 
INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[学号] ,[性别] ,[班级] ,[出生日期] )
VALUES('李晓峰3',6083,'男','计算机3','2013-05-06')
 
INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[学号] ,[性别] ,[班级] ,[出生日期] )
VALUES('张晓',6084,'女','美术','2013-05-07')
 
--插入【FENSHU_TAB】表中的5条记录
INSERT INTO [DBO].FENSHU_TAB ([学号] ,[语文] ,[数学] ,[英语] )
VALUES(6080,99.5,98.6,59.2)
 
INSERT INTO [DBO].FENSHU_TAB ([学号] ,[语文] ,[数学] ,[英语] )
VALUES(6081,93.5,94.3,55.8)
 
INSERT INTO [DBO].FENSHU_TAB ([学号] ,[语文] ,[数学] ,[英语] )
VALUES(6082,96.5,78.6,58.5)
 
INSERT INTO [DBO].FENSHU_TAB ([学号] ,[语文] ,[数学] ,[英语] )
VALUES(6083,99.5,68.4,89.2)
 
INSERT INTO [DBO].FENSHU_TAB ([学号] ,[语文] ,[数学] ,[英语] )
VALUES(6084,99.7,98.7,59.4)
GO

【信息表】的数据:

【明细分数表】的数据:

2.2.运算记录【综合分数表】的数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
插入【ZHONGHE_TAB】中的数据
USE [SQL-LI]
--声明3个变量分别用来接收【平均分】,【总分】,【姓名】,和一个控制循环的条件变量@I_WHILE_XUEHAO
DECLARE @I_WHILE_XUEHAO INT,@ZONGFEN DECIMAL(4,1),@AVGFEN DECIMAL(3,1),@XINGMING NVARCHAR(10);
SELECT @I_WHILE_XUEHAO =6080;
--使这个变量【@I_WHILE_XUEHAO】的值指定在【学号】字段上
WHILE(@I_WHILE_XUEHAO >=6080 AND @I_WHILE_XUEHAO <6085)
BEGIN
--求取【平均分】,【总分】,【姓名】并存在声明的变量中
SELECT @ZONGFEN =(F.语文 +F.数学 +F.英语 ),@AVGFEN =(F.语文 +F.数学 +F.英语 )/3,@XINGMING =X.姓名
FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.学号 =F.学号
WHERE X.学号 =@I_WHILE_XUEHAO --与【学号同步】
--将其变量的数据插入到【ZHONGHE_TAB】的对应字段上
INSERT INTO [DBO].ZHONGHE_TAB ([姓名] ,[学号] ,[平均分] ,[总分] )
VALUES(@XINGMING ,@I_WHILE_XUEHAO ,@AVGFEN ,@ZONGFEN )
SELECT @I_WHILE_XUEHAO +=1;  --与【学号同步】
END
GO

【综合分数表】的数据:

3.1.1.创建3个表关联的视图:

1
2
3
4
5
6
7
8
9
USE [SQL-LI]
GO
CREATE VIEW SHITU_FFENSHU_XINXI(姓名,学号,平均分,总分,班级,出生日期)
AS
SELECT TOP 800 X.姓名 ,F.学号 ,Z.平均分 ,Z.总分 ,X.班级 ,X.出生日期
FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.学号 =F.学号
  INNER JOIN [DBO].ZHONGHE_TAB AS Z ON F.学号 =Z.学号
  ORDER BY F.学号 ASC
  GO

查看创建的视图:

3.2.1.通过视图修改多个数据表的信息????:

1
2
3
4
5
UPDATE [SQL-LI].[dbo].[SHITU_FFENSHU_XINXI]
SET [姓名] = 'aaaaa', --此字段在【信息表】中
[平均分] =111  --此次字段在【分数】中
WHERE [学号]=6080
GO

结果:

下面就写个利用触发器对其多表进行更新的方法:

a.这里就利用instead of 代替触发来代替对各表中的字段内的信息进行修改:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
USE [SQL-LI]
GO
CREATE TRIGGER TRIGG_UPDATE --创建一个upda触发器DML
--关联到[SHITU_FFENSHU_XINXI]视图上
ON[DBO].[SHITU_FFENSHU_XINXI]
INSTEAD OF UPDATE --代替触发器执行UPDATE功能;【但是只能定义一个增删改的INSTEAD OF代替触发】。
AS
--声明接受变量用于存储【inserted】表上的数据
DECLARE @XINGMING NVARCHAR(10),@XUEHAO INT,@AVGFEN DECIMAL(3,1),@ZONGFEN DECIMAL(4,1),
    @BANJI NVARCHAR(20),@CHUSHENGRIQI DATE;
    --筛选【inserted】表中学号最小的一行数据
SELECT @XUEHAO =MIN(学号) FROM[inserted]
--遍历【inserted】表
 
WHILE(@XUEHAO IS NOT NULL)
BEGIN
--将【inserted】表中的数据存放到相应的变量中
SELECT @XUEHAO =MIN([学号])FROM[inserted] WHERE [学号]=@XUEHAO
SELECT @XINGMING=[姓名] FROM[inserted] WHERE[学号] =@XUEHAO
SELECT @AVGFEN=[平均分]FROM[inserted] WHERE[学号] =@XUEHAO
SELECT @ZONGFEN=[总分] FROM[inserted] WHERE[学号] =@XUEHAO
SELECT @BANJI =[班级]FROM[inserted] WHERE[学号] =@XUEHAO
SELECT @CHUSHENGRIQI=[出生日期]FROM[inserted] WHERE[学号] =@XUEHAO
 
--找出视图中的字段对应相应表的字段
/*因为视图中的[姓名]/[班级]/[出生日期]字段是XINXIN_TAB 中的字段,故修改【XINXIN_TAB】中对应的字段
数据之*/
UPDATE[DBO].XINXIN_TAB
SET [姓名]=@XINGMING ,[班级]=@BANJI ,[出生日期]=@CHUSHENGRIQI
WHERE[学号]=@XUEHAO
 
--道理同上
UPDATE[DBO].FENSHU_TAB
SET[学号]=@XUEHAO
WHERE[学号]=@XUEHAO
 
--道理同上
UPDATE[DBO].ZHONGHE_TAB
SET[平均分]=@AVGFEN ,[总分]=@ZONGFEN
WHERE[学号]=@XUEHAO
 
--修改完成后就开始筛选【inserted】表中下一条数据记录
SELECT @XUEHAO =MIN([学号])FROM[inserted] WHERE[学号]>@XUEHAO
--然后给while中判断
END
GO

a1.注意的是视图不是数据表没有存放数据,将从【inserted】表中的数据提取后赋给对应数据表内的字段中;



对象资源管理器中的图示:

 3.万事俱备,开始通过视图修改多表中的数据(验证):

a

1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE [SQL-LI]
--查看未修改前的视图数据信息
SELECT* FROM[DBO].SHITU_FFENSHU_XINXI
GO
 
UPDATE[DBO].SHITU_FFENSHU_XINXI
--修改【SHITU_FFENSHU_XINXI】中对应的字段数据
SET[姓名]='liyifeng' ,[平均分]=66.6 ,[总分]=88.8 ,[班级]='计算机SQLServer' ,[出生日期]='2013-05-05'
--修改筛选
WHERE[学号]=6080
GO
--查看修改后的视图数据信息
SELECT* FROM[DBO].SHITU_FFENSHU_XINXI
GO

修改前后对比的结果图示:



修改后的数据表中的数据:

1
2
3
4
5
USE [SQL-LI]
SELECT* FROM[XINXIN_TAB] WHERE[学号]=6080
SELECT* FROM[FENSHU_TAB] WHERE[学号]=6080
SELECT* FROM[ZHONGHE_TAB] WHERE[学号]=6080
GO



4.触发器在数据库里面就像颗炸弹一样,只要满足气要求就会被触发,就会对数据库里面的数据进行触发修改,所以不需要室就尽量将其关闭掉,用的时候就将其开启:

关闭:

1
2
3
4
5
USE [SQL-LI]
GO
DISABLE TRIGGER [DBO].TRIGG_UPDATE --关闭触发器【TRIGG_UPDATE】
ON SHITU_FFENSHU_XINXI
GO

开启:

1
2
3
4
5
USE [SQL-LI]
GO
ENABLE TRIGGER [DBO].TRIGG_UPDATE --开启触发器【TRIGG_UPDATE】
ON[DBO].[SHITU_FFENSHU_XINXI] --触发器所在的视图
GO

GO

Java免费学习   Java自学网 http://www.javalearns.com

关注微信号:javalearns   随时随地学Java

或扫一扫

随时随地学Java

作者:不详 来源:网络
    你是从哪里知道本网站的?
  • 网友介绍的
  • 百度搜索的
  • Google搜索的
  • 其它搜索过来的
  • 网址输错了进来的
  • 太忙了不记得了
共有评论 0相关评论
发表我的评论
  • 大名:
  • 内容:
本类推荐
  • 没有
本类固顶
  • 没有
  • java学习网(www.javalearns.com) © 2014 版权所有 All Rights Reserved.
  • Email:javalearns@163.com 站长QQ:1356121699 晋ICP备14003680号-3
  • java学习网部分内容来自网络或网友发布,如侵犯了您利益,请发邮件至:javalearns@126.com,我们尽快处理!
  • Java学习网
  • 网站统计
  • 晋公网安备 14042902000001号