SQL Server uses triggers to update multi-table views.

SQL Server uses triggers to update multi-table views.

The procedure is to use the update trigger to generate two virtual tables "inserted" to store the modified data information and the "deleted" table, then, update the corresponding data to the field information in the corresponding data table;

1. Create three tables first:

A. Information table:

USE [SQL-LI] begin transaction CHUANGJIAN_XINXIN_TAB -- CREATE a data TABLE named "XINXIN_TAB" and do NOT allow the field to be empty create table XINXIN_TAB (name NVARCHAR (10) not null, Gender NVARCHAR (1) not null, student id int not null, class NVARCHAR (20) not null, birth date not null, CONSTRAINT XUEHAO_YUESU primary key clustered ([student ID] ASC) commit transaction CHUANGJIAN_XINXI_TABGO

B. Detailed score table:

USE [SQL-LI] create table FENSHU_TAB ([student ID] int not null, [language] DECIMAL () not null, [mathematics] DECIMAL () not null, [English] DECIMAL (3,1) not null) GO

C. Comprehensive score table:

USE [SQL-LI] create table ZHONGHE_TAB ([name] NVARCHAR (10) not null, [student ID] int not null, [total score] DECIMAL (4, 1) not null, [average score] DECIMAL (3,1) not null) GO

. Insert data in the corresponding table in the table of information table and detailed score table:

USE [SQL-LI] -- INSERT five records in the [XINXIN_TAB] Table insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [Class], [Date of Birth]) VALUES ('Li Xiaofeng ', 6080, 'male', 'compute ', '1970-2013 ') insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [Class], [Date of Birth]) VALUES ('Li Xiaofeng 1 ', 6081, 'male ', 'computer 1', '2017-05-04 ') insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [Class], [Date of Birth]) VALUES ('Li Xiaofeng 2', 6082, 'male ', 'computer 2', '1970-2013 ') insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [Class], [Date of Birth]) VALUES ('Li Xiaofeng 3', 6083, 'male ', 'computer 3', '2017-05-06 ') insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [Class], [Date of Birth]) VALUES ('zhang Xiao ', 6084, 'female', 'artbe ', '1970-2013 ') -- INSERT five records in the [FENSHU_TAB] table INTO [DBO]. FENSHU_TAB ([student ID], [Chinese], [mathematics], [English]) VALUES (6080, 99.5, 98.6, 59.2) insert into [DBO]. FENSHU_TAB ([student ID], [Chinese], [mathematics], [English]) VALUES (6081, 93.5, 94.3, 55.8) insert into [DBO]. FENSHU_TAB ([student ID], [Chinese], [mathematics], [English]) VALUES (6082, 96.5, 78.6, 58.5) insert into [DBO]. FENSHU_TAB ([student ID], [Chinese], [mathematics], [English]) VALUES (6083, 99.5, 68.4, 89.2) insert into [DBO]. FENSHU_TAB ([student ID], [Chinese], [mathematics], [English]) VALUES (6084, 99.7, 98.7, 59.4) GO

Data in the [info table:

Detailed score table data:

2. Operation records the data in the comprehensive score table:

Insert data in [ZHONGHE_TAB] USE [SQL-LI] -- declare three variables are used to receive [average score], [total score], [name ], and the condition variable @ alias @ I _WHILE_XUEHAO INT, @ ZONGFEN DECIMAL (4, 1), @ AVGFEN DECIMAL (3, 1), @ XINGMING NVARCHAR (10); SELECT @ alias = 6080; -- make the value of this variable "@ I _WHILE_XUEHAO" specified in the "student ID" field WHILE (@ I _WHILE_XUEHAO> = 6080 AND @ I _WHILE_XUEHAO <6085) BEGIN -- obtain the "average ], [total score], [name], and the declared variables include SELECT @ ZONGFEN = (F. language + F. mathematics + F. english), @ AVGFEN = (F. language + F. mathematics + F. english)/3, @ XINGMING = X. name FROM [DBO]. XINXIN_TAB as x inner join [DBO]. FENSHU_TAB as f on x. student ID = F. student id where x. student ID = @ I _WHILE_XUEHAO -- synchronize with student id -- INSERT the data of the variable to the corresponding field of ZHONGHE_TAB INTO [DBO]. ZHONGHE_TAB ([name], [student ID], [average score], [total score]) VALUES (@ XINGMING, @ I _WHILE_XUEHAO, @ AVGFEN, @ ZONGFEN) SELECT @ region + = 1; -- synchronize with the student ID: ENDGO

Data in the comprehensive score table:

3.1.1. Create a view associated with three tables:

USE [SQL-LI] gocreate view SHITU_FFENSHU_XINXI (name, student ID, average score, total score, class, date of birth) asselect top 800 X. name, F. student ID, Z. average score, Z. total score, X. class, X. date of birth FROM [DBO]. XINXIN_TAB as x inner join [DBO]. FENSHU_TAB as f on x. student ID = F. student id inner join [DBO]. ZHONGHE_TAB as z on f. student ID = Z. student id order by f. student ID ASC GO

View the created View:

3.2.1. Modify the information of multiple data tables through the view ???? :

UPDATE [SQL-LI]. [dbo]. [SHITU_FFENSHU_XINXI] SET [name] = 'aaaaa', -- this field is [average score] = 111 in [information table] -- WHERE [student ID] = 6080GO in [score]

Result:

The following describes how to update multiple tables using triggers:

A. Here we use instead of trigger to replace the Information in fields in each table:

USE [SQL-LI] gocreate trigger TRIGG_UPDATE -- create a upda trigger dml -- associate with the [SHITU_FFENSHU_XINXI] view ON [DBO]. [SHITU_FFENSHU_XINXI] instead of update -- replace the trigger to execute the UPDATE function. [However, only one instead of addition, deletion, and modification can be defined to replace the trigger ]. AS -- DECLARE that the accept variable is used to store data in the inserted Table DECLARE @ xingming nvarchar (10), @ xuehao int, @ avgfen decimal (), @ zongfen decimal ), @ banji nvarchar (20), @ chushengriqi date; -- filter the data in the row with the smallest student ID in the inserted Table. SELECT @ XUEHAO = MIN (student ID) FROM [inserted] -- traverse the [inserted] Table WHILE (@ xuehao is not null) BEGIN -- store the data in the [inserted] Table to the corresponding variable. SELECT @ XUEHAO = MIN ([student ID]) FROM [inserted] WHERE [student ID] = @ xuehao select @ XINGMING = [name] FROM [inserted] WHERE [student ID] = @ xuehao select @ AVGFEN = [average score] FROM [inserted] WHERE [student ID] = @ xuehao select @ ZONGFEN = [total score] FROM [inserted] WHERE [student ID] = @ xuehao select @ BANJI = [Class] FROM [inserted] WHERE [student ID] = @ xuehao select @ CHUSHENGRIQI = [Date of Birth] FROM [inserted] WHERE [student ID] = @ XUEHAO -- find the field in the view that corresponds to the field in the corresponding table/* because [name] In the view /[Class]/[Date of Birth] field is the field in XINXIN_TAB, therefore, modify the corresponding field data in "XINXIN_TAB" */UPDATE [DBO]. XINXIN_TAB SET [name] = @ XINGMING, [Class] = @ BANJI, [Date of Birth] = @ chushengriqi where [student ID] = @ XUEHAO-Same principle as UPDATE [DBO]. FENSHU_TAB SET [student ID] = @ xuehao where [student ID] = @ XUEHAO -- same principle as above: UPDATE [DBO]. ZHONGHE_TAB SET [average score] = @ AVGFEN, [total score] = @ zongfen where [student ID] = @ XUEHAO -- filter the next data record in the inserted Table after modification. SELECT @ XUEHAO = MIN ([student ID]) FROM [inserted] WHERE [student ID]> @ XUEHAO -- then judge the ENDGO

A1. note that the view does not store data in the data table. The data in the inserted Table is extracted and assigned to the fields in the corresponding data table;

Figure in object Resource Manager:

3. Everything is ready. You can use the view to modify data in multiple tables (verification ):

A

USE [SQL-LI] -- View data information before modification SELECT * FROM [DBO]. SHITU_FFENSHU_XINXI GOUPDATE [DBO]. SHITU_FFENSHU_XINXI -- modify the field data SET [name] = 'liyunifeng' in [SHITU_FFENSHU_XINXI], [average score] = 66.6, [total score] = 88.8, [Class] = 'sqlserver SQLServer ', [Date of Birth] = '2014-05-05 '-- modify and filter WHERE [student ID] = 2013 GO -- view the modified view data SELECT * FROM [DBO]. SHITU_FFENSHU_XINXI GO

The comparison results are as follows:

Data in the modified data table:

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

4. the trigger is like a bomb in the database. As long as the gas requirements are met, the trigger will be triggered and the data in the database will be modified, therefore, you do not need to close the room as much as possible and enable it when using it:

Close:

USE [SQL-LI] godisable trigger [DBO]. TRIGG_UPDATE -- close TRIGGER [TRIGG_UPDATE] ON SHITU_FFENSHU_XINXIGO

Enable:

USE [SQL-LI] goenable trigger [DBO]. TRIGG_UPDATE -- enable TRIGGER [TRIGG_UPDATE] ON [DBO]. [SHITU_FFENSHU_XINXI] -- View where the TRIGGER is located GO

GO

I hope I can solve some problems for you! Thank you!