SQLServer利用触发器对多表视图进行更新的实现方法

Warning (2): Undefined array key "nsort" [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]

          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span>

include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 174
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Notice: file_put_contents() [function.file-put-contents]: Write of 2423 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140

Warning (2): Trying to access array offset on value of type null [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]Code Context          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span> 
include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 174
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Notice: file_put_contents() [function.file-put-contents]: Write of 2447 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
">

Warning (2): Undefined array key "nsort" [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]Code Context          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span> 
include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 174
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Warning (2): Trying to access array offset on value of type null [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]Code Context          <div class="info"> 
            <span><a href="<?php echo $website_info[0]["nsort"]["nsort_url"];?>"><i class="fa fa-columns"></i><?php echo $website_info[0]["nsort"]["nsort_name"];?></a></span> 
            <span><i class="fa fa-clock-o"></i><?php echo $website_info[0]["submission_date"];?></span> 
include - ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430
Cake\View\View::_evaluate() - CORE/src/View/View.php, line 1184
Cake\View\View::_render() - CORE/src/View/View.php, line 1138
Cake\View\View::render() - CORE/src/View/View.php, line 769
Cake\Controller\Controller::render() - CORE/src/Controller/Controller.php, line 762
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3938
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 174
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

其步骤就是：利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表，然后将对应的数据更新到对应数据表中的字段信息中；1.首先创建3个表：a.信息表：USE[SQL-LI]BEGINTRANSACTIONCHUANGJIAN_XINXIN_TAB--创建命名为【XINXIN_TAB】的数据表，同时不允许字段为空CREATETABLEXINXIN_TAB(姓名NVARCHAR(10)NOTNULL,性别NVARCHAR(1)NOTNULL,学号INTNOTNULL,班级NVARCHAR(20)NOTNULL,出生日期DATENOTNULL,CONSTRAINTXUEHAO_YUESUPRIMARYKEYCLUSTERED([学号]ASC))COMMITTRANSACTIONCHUANGJIAN_XINXI_TABGOb.明细分数表：USE[SQL-LI]CREATETABLEFENSHU_TAB([学号]INTNOTNULL,[语文]DECIMAL(3,1)NOTNULL,[数学]DECIMAL(3,1)NOTNULL,[英语]DECIM

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

1.首先创建3个表：

a.信息表：

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_TABGO

b.明细分数表：

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.综合分数表：

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.【信息表】和【明细分数表】插入对应表中的数据：

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.运算记录【综合分数表】的数据：

插入【ZHONGHE_TAB】中的数据USE [SQL-LI] --声明3个变量分别用来接收【平均分】，【总分】，【姓名】，和一个控制循环的条件变量@I_WHILE_XUEHAODECLARE @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;  --与【学号同步】ENDGO

【综合分数表】的数据：

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

USE [SQL-LI] GOCREATE VIEW SHITU_FFENSHU_XINXI(姓名,学号,平均分,总分,班级,出生日期)ASSELECT 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.通过视图修改多个数据表的信息????：

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

结果：

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

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

USE [SQL-LI] GOCREATE 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中判断 ENDGO

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

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

3.万事俱备，开始通过视图修改多表中的数据（验证）：

USE [SQL-LI] --查看未修改前的视图数据信息SELECT* FROM[DBO].SHITU_FFENSHU_XINXI GOUPDATE[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

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

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

USE [SQL-LI] SELECT* FROM[XINXIN_TAB] WHERE[学号]=6080SELECT* FROM[FENSHU_TAB] WHERE[学号]=6080SELECT* FROM[ZHONGHE_TAB] WHERE[学号]=6080GO

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

关闭：

USE [SQL-LI] GODISABLE TRIGGER [DBO].TRIGG_UPDATE --关闭触发器【TRIGG_UPDATE】ON SHITU_FFENSHU_XINXIGO

开启：

USE [SQL-LI] GOENABLE TRIGGER [DBO].TRIGG_UPDATE --开启触发器【TRIGG_UPDATE】ON[DBO].[SHITU_FFENSHU_XINXI] --触发器所在的视图GO

希望我写的能为你解决一点问题，还望指教！谢谢！

标签: SQLServer 利用触发器多表视图进行新的实现方法

声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！

上一篇: SQLFunction自定义函数详解

娱乐休闲

工商与经济

电脑与网络

公司与企业

教育与培训

文学

艺术

体育与健身

新闻与媒体

卫生与健康

科学/文化

生活与服务

家政服务

综合性企业

另类文学

水上运动

动漫/卡通

体育院校

出版

综合网站

网络媒体

商场市场

购物

餐饮/菜谱

SQLServer利用触发器对多表视图进行更新的实现方法

热门文章

热点

标签列表

分享到:

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索

SQLServer利用触发器对多表视图进行更新的实现方法

猜您喜欢

热门文章

热点

标签列表

分享到:

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索