Add, delete, and modify views in SQL Server

After the release of lesktop open-source im, some netizens asked how to integrate the embedded im with the user system of their websites (that is, how to make the embedded im directly use the original user database of the website, instead of importing existing user data to the IM database ). Lesktop adds, deletes, and modifies users tables (tables that store user login names, nicknames, passwords, and other information) during the storage process. Obviously, if it is difficult to directly modify the stored procedures related to the users table, this article will introduce a relatively simple method, where you do not need to modify the stored procedures andSource codeTo integrate the user system.

To achieve this goal, we will first introduce how to add, delete, and modify views in SQL Server. If the user has two items: Name and remark, but they are not stored in the same table, they are stored separately in two tables: userbase (ID, name) and userextent (ID, remark.

For ease of use, a view users is created to indicate the complete information of the user. Its definition is as follows:

 
Create View[DBO]. [users]AsSelectB. IDAsID, B. NameAsName, E. remarkAsRemarkFromUserbase B, userextent EWhereB. ID = E. ID;
 

Now, we want to use the users view to add, delete, and modify the userbase and userextent tables. Obviously, it is impossible to directly execute insert, update, and delete on users. The following error occurs during execution:

In SQL Server, you can use triggers to add, delete, and modify views. For example, you can create an insert trigger. When you execute insert on the users view, you can implement userbase in the trigger, the insert operation of userextent. In the trigger, you can obtain the newly inserted row through the table named inserted.CodeAs follows:

Create   Trigger [DBO]. [users_insert] On [DBO]. [users] instead Of Insert As  Declare @ Name nvarchar (32), @ remark nvarchar (32) Declare Ins_cursor Cursor  For  Select Name, remark From Inserted Open Ins_cursor Fetch   Next   From Ins_cursor Into @ Name, @ remark; While (@ Fetch_status = 0) Begin -- Read all rows and insert Into Userbase (name) Values (@ Name); insert Into Userextent (ID, remark) Values (@@ Identity , @ Remark ); Fetch   Next   From Ins_cursor Into @ Name, @ remark; End  Close Ins_cursor

Next we will insert two rows of data to test the trigger:

-- Clear all dataDelete FromUserextent;Delete FromUserbase;Create Table# Temp (name nvarchar (32), remark nvarchar (32) insert # temp (name, remark)Values(N'User1', N'1'); Insert # temp (name, remark)Values(N'User2', N'2'); -- Insert two rows of data insert users (name, remark)SelectName, remarkFrom# TempDrop Table# TempSelect*FromUsers;Select*FromUserbase;Select*FromUserextent;
 

The execution result is as follows:

Create an update trigger. Similar to the insert trigger, the affected rows are saved in inserted. You can obtain the affected rows from the inserted Table and update userbase and userextent. The Code is as follows:

 
Create Trigger[DBO]. [users_update]On[DBO]. [users] insteadOf UpdateAsUpdateUserextentSetUserextent. remark = ins. remarkFromInserted INSWhereUserextent. ID = ins. ID;UpdateUserbaseSetUserbase. Name = ins. NameFromInserted INSWhereUserbase. ID = ins. ID;
 

Test code:

 -- clear all data  Delete   from  userextent;  Delete   from  userbase; -- insert two rows of data into users (name, remark)  values  (n  'user1' , n  '1' ); insert users (name, remark)  values  (n  'user2' , n  '2' ); insert users (name, remark)  values  (n  'user3' , n  '2'  ); --  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 delete trigger, you can use the deleted table to obtain the row to be deleted. The Code is as follows:

Create Trigger[DBO]. [users_delete]On[DBO]. [users] insteadOf DeleteAsDelete FromUserextentWhereIDIn(SelectIDFromDeleted)Delete FromUserbaseWhereIDIn(SelectIDFromDeleted)
 

Test code:

 -- clear all data  Delete   from  userextent;  Delete   from  userbase; -- insert two rows of data into users (name, remark)  values  (n  'user1' , n  '1' ); insert users (name, remark)  values  (n  'user2' , n  '2' ); insert users (name, remark)  values  (n  'user3' , n  '2'  ); -- Delete the last two rows of Data  Delete   from  Users  where  remark = n  '2'  -- output data  select  *  from  users;  select  *  from  userbase;  select  *  from  userextent; 

Running result:

This section describes how to add, delete, and modify a view. Next, we will introduce how to integrate the lesktop open-source im user system by creating a view and adding an add, delete, and modify trigger. First, we will introduce the structure of the users table in the lesktop open source im database:

If the user table of your website (for example, myusertable) has only name, nickname:

Then, you can create an extended table (for example, userextentim) to store other information:

next, you only need to delete the users table, recreate a view named users, and use the method described above to process users, userbase, and userextent, create a trigger in the users view and add, delete, and modify the myusertable and userextentim tables in the trigger. When the lesktop Stored Procedure reads and deletes the user, the trigger is automatically converted to myusertable, userextentim operations, so you do not need to modify any stored procedures and source code, of course, will not affect your original database.