To add and remove a view in SQL Server

Original: In SQL Server, the view is being hacked

After lesktop open source IM, some netizens asked how to integrate with the user system of their website after im embedding (that is, how to let embedded im directly use the original user database of the site, without having to import the existing user data into the IM database). Lesktop to the Users table (the storage user login name, nickname, password and other information table) is stored in the process of adding and removing changes, obviously, if directly to the users table related stored procedures are more troublesome, this article will introduce a relatively simple method, Consolidate user systems without the need to modify stored procedures and source code.

To do this, let's start with a look at how to make a view in SQL Server. The fake user has name,remark two information, but is not stored in the same table, but is stored separately in two tables userbase (ID, Name), userextent (ID, Remark).

For ease of use, create a view of users that represents the complete information for the user, defined as follows:

create  view  [dbo]. [Users] as  select  b.id as  ID, b.name as  Name, E. Remark as  Remark from  UserBase B, userextent e  WHERE  b.id = e.id; 

Now, we want to modify the Userbase,userextent table by using the users view for the redaction. Obviously, if it is not possible to execute Insert,update,delete directly against users, the following error occurs when executing:

In SQL Server, the view additions and deletions can be implemented by triggers, for example, we can create an insert trigger that implements an insert operation to the userbase,userextent in the trigger when the insert is executed on the views users. In the trigger, you can get to the newly inserted row through a table named inserted, with the following code:

CREATE TRIGGER[dbo]. [Users_insert] on[dbo]. [Users] INSTEAD ofINSERT asDeclare@name nvarchar (+), @remark nvarchar (32)DeclareIns_cursorcursor forSelectName, Remark fromInsertedOpenIns_cursorFetch Next  fromIns_cursor into@name, @remark; while(@ @fetch_status = 0)begin--Reads all rows and inserts the Insert intoUserBase (Name)Values(@name); Insert intoUserextent (ID, Remark)Values(@@Identity, @remark);Fetch Next  fromIns_cursor into@name, @remark;EndCloseIns_cursor

Here we test the trigger by inserting two rows of data:

--Erase All data Delete from Userextent; Delete  from UserBase; Create table #temp (    name nvarchar,    values (n' user1 ', n' 1 'values (n' user2 ', n' 2 ');--inserting two rows of data insert Users (name, remark)Select from # TempDroptable #tempSelect from Users; Select  from UserBase; Select  from Userextent;

The results of the implementation are as follows:

Create an update trigger, similar to an INSERT trigger, the affected rows are saved in inserted, you can get the affected row from the inserted table, and update the userbase,userextent with the following code:

CREATE TRIGGER  on  of UPDATE  as Update Userextentset userextent.remark=ins. Remark from inserted inswhere userextent.id = ins.id; Update UserBaseset userbase.name=ins. Name from inserted inswhere userbase.id = ins.id;

Test code:

--Clear All data delete  from  Userextent;delete  from  userbase;--inserting two rows of data insert Users (Name,remark) values  (N ' user1 ' , N ' 1 ' ); Insert Users (Name,remark)  (n ' user2 ' , N ' 2 ' ); Insert Users (Name,remark) values  (n ' User3 ' , N ' 2 ' );--Modified two rows of data update  Users set  Remark = n ' 3 '  where  Remark = N ' 2 ' -output data select  * from  Users ; select  * from  UserBase; select  * from  userextent; 

Test results:

Create a delete trigger, in the deleted trigger, you can get the deleted row through the deleted table, the code is as follows:

CREATE TRIGGER  on  of DELETE  as Delete  from where inch (Select from deleted) Delete  from where inch (Select from deleted)

Test code:

--Clear All data delete  from  Userextent;delete  from  userbase;--inserting two rows of data insert Users (Name,remark) values  (N ' user1 ' , N ' 1 ' ); Insert Users (Name,remark)  (n ' user2 ' , N ' 2 ' ); Insert Users (Name,remark) values  (n ' User3 ' , N ' 2 ' );--delete two rows of data delete  from  Users where  Remark = N ' 2 ' --output data select  * from  Users; select  * from  UserBase; select  * from  userextent; 

Operation Result:

The above describes how to make additions and deletions to the view, and then we will introduce how to integrate the open source IM user system by establishing the views and adding additions and deletions to trigger the lesktop. First introduce the structure of the Users table in the Lesktop open-source IM database:

If the user table for your site (if the name is myusertable) is only name,nickname:

Then, you can create an extension table (if the name is Userextentim) to store additional information:

Next, you just need to delete the users table, re-establish a view called users, and then use the methods above to process users, UserBase, userextent, build a trigger on the users view, and myusertable in the trigger, Userextentim table to be modified, lesktop stored procedures for the user to read and add and delete changes, the trigger will be automatically converted to Myusertable,userextentim operation, so there is no need to modify any stored procedures and source code, Of course, it will not affect your original database.

To add and remove a view in SQL Server