Use Instead of trigger to update the view of MSSQL Server

Views are often used recently, but MSSQL Server does not allow update operations on views associated with more than two base tables. Due to the project's needs, I spent a night studying it. Take some time to record the process and experience.

Let's take a look at the authoritative saying:

 

Updatable View:

Microsoft SQL Server 2000 enhances the updatable view category in two ways:

 

Instead of trigger: you can create an instead of trigger on the view to make the view updatable. Execute the instead trigger instead of the data modification statement that defines the trigger. This trigger allows you to specify an operation to be performed when processing data modification statements. Therefore, if a view's instead of trigger exists on a given data modification Statement (insert, update, or delete), the corresponding view can be updated through this statement. For more information about the instead of trigger, see designing an instead of trigger.

 

Partition view: if a view is in a specified format called "partition View", its updatability is limited by some restrictions. This topic will discuss the partition view and its updatability later.

If necessary, SQL Server identifies the "local partition View" as a view where all participating tables and views are on the same SQL Server, the "distributed partition View" is identified as a view where at least one table exists on different (remote) servers.

 

If the view does not have an instead trigger, or the view is not a partition view, the view can be updated only when the following conditions are met:

 

Select_statement does not include aggregate functions in the selection list, and does not include top, group by, Union (unless the view is the partition view to be described later in this topic) or distinct clause. Aggregate functions can be used in subqueries of the from clause, as long as the values returned by the function are not modified. For more information, see Aggregate functions.

 

There is no derived column in The select_statement selection list. A derived column is a result set column consisting of any non-simple column expressions (using functions, addition or subtraction operators, etc.

 

The from clause in select_statement references at least one table. Select_statement must contain not only non-Table expressions (that is, expressions not derived from tables ). For example, the following views cannot be updated:

Create view notable

Select getdate () as currentdate,

@ Language as currentlanguage,

CURRENT_USER as currentuser

 

Before the insert, update, and delete statements can reference updatable views, they must meet certain restrictions as specified in the preceding conditions. The update and insert statements can reference a view only when the view can be updated and only the data in a base table referenced by the from clause of the view can be modified by the update or insert statement. Only when a view references only one table in its from clause can the delete statement reference updatable views.

 

Partition view is troublesome. This article uses the instead of trigger method to solve it!

My experiment is as follows: create two tables Table1 and Table2 first. The table structures are as follows:

Note: 1. The D field in table 2 is automatic, and the step size is the default value of 1. 2. The a field in Table1 is automatic, and the step size is the default value of 1.

Then, create view view1:

Select DBO. table2.d, DBO. table2.e, DBO. table1. B, DBO. table1.c

From DBO. Table2 inner join

DBO. Table1 on DBO. table2.a = DBO. table1.a

Obviously, this view contains four fields: D, E, B, and C.

 

Is the problem clear? How to add data to this view? After several attempts, the correct SQL statement is obtained:

Create trigger trig_ins_view1 on view1

Instead of insert

As

 

Declare @ A int

Declare @ B INT

Begin

Set nocount on

 

Insert into Table1 select B, c from inserted

Select @ A = (max (A) + 1) from Table1

Select @ B = E from inserted

Insert into Table2 (a, e) values (@ A, @ B)

 

End