SQL Server's implementation of updating multiple table views using triggers _mssql

The step is to use the 2 virtual table "inserted" generated by the update action trigger to store the modified data information and the "deleted" table, and then update the corresponding data to the field information in the corresponding datasheet;

1. Create 3 Tables First:

A. Information table:

Use [Sql-li]
BEGIN TRANSACTION chuangjian_xinxin_tab 
--Create a datasheet named "Xinxin_tab" without allowing the field to be empty
create table Xinxin_tab
(
name NVARCHAR NOT NULL,
gender NVARCHAR (1) NOT NULL,
student number INT NOT NULL,
class NVARCHAR not N ull,
birthdate date not NULL,
CONSTRAINT xuehao_yuesu PRIMARY KEY CLUSTERED
([Learn number]asc)
)
COMMIT TRANSACTION Chuangjian_xinxi_tab Go

B. Detail score Table:

Use [Sql-li] 
CREATE TABLE fenshu_tab
(
[School number] INT not NULL,
[language] DECIMAL (3,1) not NULL,
[math] Decimal (3,1) not NULL,
[English] DECIMAL (3,1) NOT null
)
go

C. Integrated score table:

Use [Sql-li] 
CREATE TABLE zhonghe_tab
(
[name] NVARCHAR () not NULL,
[School number] INT not NULL,
[total score] DECIMAL ( 4,1) not NULL,
[average score] DECIMAL (3,1) not null)
go

2.1. "Information table" and "Detail score table" Insert the data in the corresponding table:

Use [Sql-li]-Inserts 5 records from the "Xinxin_tab" table into [DBO]. Xinxin_tab ([name], [student number], [Sex], [class], [date of birth]] VALUES (' Xiaofeng ', 6080, ' Male ', ' computer ', ' 2013-05-03 ') INSERT into [DBO]. Xinxin_tab ([name], [student number], [Sex], [class], [date of birth]] VALUES (' Xiaofeng 1 ', 6081, ' Male ', ' Computer 1 ', ' 2013-05-04 ') INSERT into [DBO]. Xinxin_tab ([name], [student number], [Sex], [class], [date of birth]] VALUES (' Xiaofeng 2 ', 6082, ' Male ', ' Computer 2 ', ' 2013-05-05 ') INSERT into [DBO]. Xinxin_tab ([name], [student number], [Sex], [class], [date of birth]] VALUES (' Xiaofeng 3 ', 6083, ' Male ', ' Computer 3 ', ' 2013-05-06 ') INSERT into [DBO].  Xinxin_tab ([name], [student number], [Sex], [class], [date of birth]] VALUES (' Zhangxiao ', 6084, ' female ', ' art ', ' 2013-05-07 ')--Insert the 5 records in the "Fenshu_tab" table insert INTO [DBO]. Fenshu_tab ([School number], [Chinese], [mathematics], [English]) VALUES (6080,99.5,98.6,59.2) INSERT into [DBO]. Fenshu_tab ([School number], [Chinese], [mathematics], [English]) VALUES (6081,93.5,94.3,55.8) INSERT into [DBO]. Fenshu_tab ([School number], [Chinese], [mathematics], [English]) VALUES (6082,96.5,78.6,58.5) INSERT into [DBO]. Fenshu_tab ([School number], [Chinese], [mathematics], [English]) VALUES (6083,99.5,68.4,89.2) INSERT into [DBO]. Fenshu_tab ([School number], [Chinese], [mathematics], [English]) VALUES (6084,99.7,98.7,59.4) Go 

Data for the information table:

Data for detail score table:

2.2. The data of "Composite score table" is recorded in the Operation record:

Insert the data in "Zhonghe_tab"
use [Sql-li] 
--Declare 3 variables to receive the "Average score", "Total Score", "name", and a conditional variable that controls the loop @i_while_xuehao
DECLARE @ I_while_xuehao INT, @ZONGFEN decimal (4,1), @AVGFEN decimal (3,1), @XINGMING NVARCHAR (a);
SELECT @I_WHILE_XUEHAO =6080;
--The value of the variable "@I_WHILE_XUEHAO" is specified on the "Number" field while
(@I_WHILE_XUEHAO >=6080 and @I_WHILE_XUEHAO <6085)
BEGIN --Get the "average score", "
total Score", "name" coexist in the declared variable
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. School number =f WHERE X. Learning number = @I_WHILE_XUEHAO--and "sync with school Number"--Inserts the
data of its variable into the corresponding field of "Zhonghe_tab" INSERT into [DBO]. Zhonghe_tab ([name], [school number], [average score], [total score])
VALUES (@XINGMING, @I_WHILE_XUEHAO, @AVGFEN, @ZONGFEN)
SELECT @I_WHILE_ Xuehao +=1;  --"Sync with school number" End Go

Data for the "Composite score Table":

3.1.1. Create a view associated with 3 tables:

Use [Sql-li] 
go
CREATE VIEW shitu_ffenshu_xinxi (name, student number, average score, total score, class, birth date
)
as SELECT top X. Name, F. School Number, 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. School number =f. 
  INNER JOIN [DBO]. Zhonghe_tab as Z on F. School number =z. ORDER by 
  F. Learn number ASC go
  

To view the created view:

3.2.1. Modify the information for multiple data tables through a view???? ：

UPDATE [Sql-li]. [dbo]. [Shitu_ffenshu_xinxi]
SET [Name] = ' AAAAA ',--this field in the Information table
[average] =111  --This field in the "score"
where [school number]=6080
go

Results:

Here's a way to use triggers to update multiple tables:

A. Here is the use of instead of substitution triggers to modify the information in the fields in each table:

Use [Sql-li] go create TRIGGER trigg_update--Create a UPDA trigger DML--associated with Shitu_ffenshu_xinxi on the [on[dbo] view.
[Shitu_ffenshu_xinxi]
INSTEAD of update--performs the update function instead OF triggers; "But you can only define a INSTEAD of substitution for the trigger."
    As--declares that the accept variable is used to store the data on the "inserted" table DECLARE @XINGMING NVARCHAR (a), @XUEHAO INT, @AVGFEN decimal (3,1), @ZONGFEN decimal (4,1),
    @BANJI NVARCHAR, @CHUSHENGRIQI DATE; --Filter the "inserted" table the smallest line of data SELECT @XUEHAO =min (school number) from[inserted]--traverse the "inserted" table while (@XUEHAO are not NULL) BEGIN--will "in Serted the data in the table is stored in the corresponding variable select @XUEHAO =min ([school number]) from[inserted] WHERE [Learning number]= @XUEHAO select @XINGMING =[name] from[inserted] where[] = @XUEHAO Select @AVGFEN =[average]from[inserted] where[number] = @XUEHAO Select @ZONGFEN =[total score] from[inserted) = @XUEHAO Select @BANJI =[class]from[inserted] "where[" = @XUEHAO Select @CHUSHENGRIQI =[Birth date]from[inserted] where[School number] = @XUEH AO--Find the fields in the view that correspond to the corresponding table/* because the [name] [class]/Birth date field in the view is a field in Xinxin_tab, so modify the corresponding field data in "Xinxin_tab"/update[dbo]. Xinxin_tab SET [Name]= @XINGMING, [Class]= @BANJI, [Birth date]=@cHushengriqi where[]= @XUEHAO------------ditto update[dbo]. Fenshu_tab set[School Number]= @XUEHAO where[School Number]= @XUEHAO-the Truth ditto Update[dbo]. Zhonghe_tab set[Average]= @AVGFEN, [total score]= @ZONGFEN where[School Number]= @XUEHAO--After the modification is complete, filter the next data record in the inserted table select @XUEHAO =min ([School Number ] from[inserted] where[number]> @XUEHAO--and then to the while to determine the end go

A1. Note that the view is not a data table does not hold data, will be extracted from the "inserted" table of data after the assignment to the corresponding data table in the field;

Diagram in Object Explorer:

3. Everything is ready to start by modifying the data in multiple tables (validation) through the view:

A

Use [Sql-li] 
--View data information that is not modified before
select* from[dbo]. Shitu_ffenshu_xinxi 
go

update[dbo]. Shitu_ffenshu_xinxi 
--Modify the corresponding field data in "Shitu_ffenshu_xinxi"
set[name]= ' Liyifeng ', [average]=66.6, [total score]=88.8, [Class]= '] Computer SQL Server ', [Birth date]= ' 2013-05-05 ' 
--Modify filter
where[number]=6080 
go
-View Modified view data
select* DBO]. Shitu_ffenshu_xinxi Go 

The results of the comparison before and after the change diagram:

Data in the modified datasheet:

Use [Sql-li] 
select* From[xinxin_tab] where[school]=6080 select* From[fenshu_tab
] where[School Number]=6080
From[zhonghe_tab] where[,]=6080 go

4. Triggers in the database inside like a bomb, as long as the gas requirements will be triggered, the database will trigger changes in the data, so do not need the room as far as possible to close it off, use the time to open it:

Shut down:

Use [Sql-li] 
go
DISABLE TRIGGER [DBO]. Trigg_update--Close trigger "trigg_update" on
shitu_ffenshu_xinxi
go

Open:

Use the [Sql-li] go
ENABLE TRIGGER [DBO]. Trigg_update--Open trigger "trigg_update"
on[dbo].[ Shitu_ffenshu_xinxi]--The view where the trigger is located go

Go

I hope I can write for you to solve a problem, but also hope that advice! Thank you!