View update using instead OF trigger on the view of MSSQL server

server| View
View is often used recently, but MSSQL server does not allow the update of view that is more than two base tables associated with it. As a result of the project needs, spent a night to study it. Take some time to record the process and the experience.

Let's look at the authoritative statement:



Updatable views:

Microsoft SQL Server 2000 enhances the categories of updatable views in two ways:



INSTEAD of triggers: INSTEAD of triggers can be created on the view to make the view updatable. Executes the INSTEAD of trigger rather than executing the data modification statement that defines the trigger. The trigger allows the user to specify a set of actions to be performed when processing data modification statements. Therefore, if the INSTEAD of the view is present on a given data modification statement (INSERT, UPDATE, or DELETE), the corresponding view can be updated by the statement. For more information about INSTEAD of triggers, see Designing INSTEAD of Triggers.



Partitioned view: If the view belongs to a specified format called a partitioned view, the updatable nature of the view is limited by some restrictions. The partitioned view and its availability are discussed later in this topic.

When necessary, SQL Server distinguishes the local partitioned view from the view that all participating tables and views are on the same SQL Server, and the distributed partitioned view is identified as a view in which at least one table resides on a different (remote) server.



If the view does not have a INSTEAD of trigger, or if the view is not a partitioned view, the view can be updated only if the following conditions are true:



Select_statement does not have aggregate functions in the select list and does not include top, GROUP by, UNION (unless the view is a partitioned view that is described later in this topic) or a DISTINCT clause. Aggregate functions can be used in subqueries of the FROM clause, as long as the value returned by the function is not modified. For more information, see Aggregate functions.



There are no derived columns in the select_statement selection list. A derived column is a result set column that is composed of any non-simple column expressions (using functions, addition or subtraction operators, and so on).



The FROM clause in select_statement references at least one table. Select_statement must not only contain expressions that are not tabular (that is, an expression that is not derived from a table). For example, the following views are not updatable:

CREATE VIEW Notable as

SELECT GETDATE () as currentdate,

@ @LANGUAGE as Currentlanguage,

Current_User as CurrentUser



INSERT, UPDATE, and DELETE statements must also meet certain restrictions, as specified in the preceding conditions, before they can refer to updatable views. The update and INSERT statements can refer to a view only if the view is updatable and the write update or INSERT statement modifies the data in one of the base tables referenced by the view's FROM clause. The DELETE statement can refer to updatable views only when the view refers to only one table in its from clause.



Partitioned view is troublesome, this article uses the instead of the trigger method to solve!

My experiment is as follows: first set up two table Table1, Table2. The table structure is as follows:



Note:

1, where the D field in Table2 is automatic, step is the default value of 1

2, the A field in Table1 is automatic, step is the default value 1



Then, set up the 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, the view contains field d,e,b,c four fields



The question is clear? How do you add data to this view? Several attempts to finally get the correct SQL for:

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