SQL Server uses triggers to update multi-table views

1. Create three tables first:

A. Information table:

1 USE [SQL-LI]
2 begin transaction CHUANGJIAN_XINXIN_TAB
3 -- create a data table named "XINXIN_TAB" and do not allow blank fields
4 create table XINXIN_TAB
5 (
6 Name NVARCHAR (10) not null,
7 Gender NVARCHAR (1) not null,
8 Student id int not null,
9 class NVARCHAR (20) not null,
10 DATE of birth not null,
11 CONSTRAINT XUEHAO_YUESU PRIMARY KEY CLUSTERED
12 ([student ID] ASC)
13)
14 commit transaction CHUANGJIAN_XINXI_TAB
15 GOb. Detailed score table:

USE [SQL-LI]
Create table FENSHU_TAB
(
[Student ID] int not null,
[Language] DECIMAL (3,1) not null,
[Mathematics] DECIMAL (3,1) not null,
[English] DECIMAL (3,1) NOT NULL
)
GOc. 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)
GO2.1. insert data in the corresponding table in the "information table" and "detail score table:

Insert data from the info table and the detail score table.
USE [SQL-LI]
-- Insert 5 records in the [XINXIN_TAB] table
Insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [class], [date of birth])
VALUES ('li Xiaofeng ', 6080, 'male', 'computer', '2017-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', '2017-2013 ')

Insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [class], [date of birth])
VALUES ('li Xiaofeng 3', 6083, 'male', 'Computer 3', '2017-2013 ')

Insert into [DBO]. XINXIN_TAB ([name], [student ID], [gender], [class], [date of birth])
VALUES ('Zhang Xiao', 6084, 'female ', 'artbe', '2017-2013 ')

-- Insert five records in the [FENSHU_TAB] table
Insert into [DBO]. FENSHU_TAB ([student ID], [language], [Mathematics], [English])
VALUES (6080, 99.5, 98.6, 59.2)

Insert into [DBO]. FENSHU_TAB ([student ID], [language], [Mathematics], [English])
VALUES (6081, 93.5, 94.3, 55.8)

Insert into [DBO]. FENSHU_TAB ([student ID], [language], [Mathematics], [English])
VALUES (6082, 96.5, 78.6, 58.5)

Insert into [DBO]. FENSHU_TAB ([student ID], [language], [Mathematics], [English])
VALUES (6083, 99.5, 68.4, 89.2)

Insert into [DBO]. FENSHU_TAB ([student ID], [language], [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]
1 USE [SQL-LI]
2 -- declare three variables to receive the [average score], [total score], [name], and a condition variable that controls the loop @ I _WHILE_XUEHAO
3 DECLARE @ I _WHILE_XUEHAO INT, @ ZONGFEN DECIMAL (4, 1), @ AVGFEN DECIMAL (3, 1), @ XINGMING NVARCHAR (10 );
4 SELECT @ I _WHILE_XUEHAO = 6080;
5 -- set the value of the variable "@ I _WHILE_XUEHAO" to the "student ID" field.
6 WHILE (@ I _WHILE_XUEHAO> = 6080 AND @ I _WHILE_XUEHAO <6085)
7 BEGIN
8 -- calculate the [average score], [total score], and [name], which are included in the declared variables
9 SELECT @ ZONGFEN = (F. language + F. mathematics + F. english), @ AVGFEN = (F. language + F. mathematics + F. english)/3, @ XINGMING = X. name
10 FROM [DBO]. XINXIN_TAB as x inner join [DBO]. FENSHU_TAB as f on x. Student ID = F. Student ID
11 where x. Student ID = @ I _WHILE_XUEHAO -- synchronize with the student ID]
12 -- insert the data of the variable to the corresponding field of ZHONGHE_TAB.
13 insert into [DBO]. ZHONGHE_TAB ([name], [student ID], [average score], [total score])
14 VALUES (@ XINGMING, @ I _WHILE_XUEHAO, @ AVGFEN, @ ZONGFEN)
15 SELECT @ I _WHILE_XUEHAO + = 1; -- synchronize with the student ID]
16 END
17 GO [comprehensive score table] data:

 

3.1.1. Create a view associated with three tables:

1 USE [SQL-LI]
2 GO
3 create view SHITU_FFENSHU_XINXI (name, student ID, average score, total score, class, date of birth)
4
5 select top 800 X. Name, F. Student ID, Z. Average score, Z. Total score, X. Class, X. Date of birth
6 FROM [DBO]. XINXIN_TAB as x inner join [DBO]. FENSHU_TAB as f on x. Student ID = F. Student ID
7 inner join [DBO]. ZHONGHE_TAB as z on f. Student ID = Z. Student ID
8 order by f. Student ID ASC
9 GO
10. View the created view:

 

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

1 UPDATE [SQL-LI]. [dbo]. [SHITU_FFENSHU_XINXI]
2 SET [name] = 'aaaaa', -- this field is in the [information table]
3 [average score] = 111 -- this field is in [score]
4 WHERE [student ID] = 6080
5 GO results:

 

 

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]
GO
Create 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 add, delete, and modify instead of can be defined to replace the trigger ].
AS
-- Declares that the accept variable is used to store data in the inserted table.
DECLARE @ xingming nvarchar (10), @ xuehao int, @ avgfen decimal (3, 1), @ zongfen decimal (4, 1 ),
@ Banji nvarchar (20), @ chushengriqi date;
-- Filter 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 fields in the view corresponding to the corresponding table fields
/* Because the [name]/[class]/[date of birth] field in the view is a field in XINXIN_TAB, modify the corresponding field in XINXIN_TAB.
Data */
UPDATE [DBO]. XINXIN_TAB
SET [name] = @ XINGMING, [class] = @ BANJI, [date of birth] = @ CHUSHENGRIQI
WHERE [student ID] = @ XUEHAO

-- Same as above
UPDATE [DBO]. FENSHU_TAB
SET [student ID] = @ XUEHAO
WHERE [student ID] = @ XUEHAO

-- Same as above
UPDATE [DBO]. ZHONGHE_TAB
SET [average score] = @ AVGFEN, [total score] = @ ZONGFEN
WHERE [student ID] = @ XUEHAO

-- After modification, filter the next data record in the inserted table.
SELECT @ XUEHAO = MIN ([student ID]) FROM [inserted] WHERE [student ID]> @ XUEHAO
-- Then judge for while
END
GOa1. 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 before modification
SELECT * FROM [DBO]. SHITU_FFENSHU_XINXI
GO

UPDATE [DBO]. SHITU_FFENSHU_XINXI
-- Modify the field data in [SHITU_FFENSHU_XINXI]
SET [name] = 'liyunifeng', [average score] = 66.6, [total score] = 88.8, [class] = 'sqlserver SQLServer ', [date of birth] = '2017-05-05'
-- Modify and filter
WHERE [student ID] = 6080
GO
-- View the modified view data
SELECT * FROM [DBO]. SHITU_FFENSHU_XINXI
The following figure shows the comparison results before and after GO modification:

 

Data in the modified data table:

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

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]
GO
Disable trigger [DBO]. TRIGG_UPDATE -- disable trigger [TRIGG_UPDATE]
ON SHITU_FFENSHU_XINXI
GO

Enable:

USE [SQL-LI]
GO
Enable trigger [DBO]. TRIGG_UPDATE -- enable trigger [TRIGG_UPDATE]
ON [DBO]. [SHITU_FFENSHU_XINXI] -- view of the trigger
GO

GO