配置SQL SERVER垂直分片
SQL SERVER垂直分片
垂直分片比水平分片要复杂一下,主要是写触发器,我头都大了,不多解释了,直接贴代码:
A:
B:
A:
Code
1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 EXECUTE sp_serveroption @server='RENHU\A',@optname='lazy schema validation',@optvalue='true'
9 GO
10
11 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
12 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
13 GO
14
15 CREATE DATABASE MEDIA;
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到B
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\B')
23 EXECUTE sp_addlinkedserver 'RENHU\B', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\B','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY IDENTITY(1,1),
31 [USER_NAME] NVARCHAR(20) NOT NULL,
32 [USER_PWD] VARBINARY NOT NULL,
33 [USER_ROLE] INT NOT NULL); -- 类别,0代表管理员,1代表普通用户
34 GO
35
36 -- 建立用户
37 CREATE USER XQLS FROM LOGIN xqls;
38 GO
39
40 -- 分配权限
41 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
42 GO
43
44 -- 建立视图
45 CREATE VIEW [USER_VIEW]
46 AS
47 SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
48 A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
49 B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
50 B.[USER_GENDER] AS [USER_GENDER], B.[USER_DES] AS [USER_DES]
51 FROM MEDIA.dbo.[USER] A, [RENHU\B].MEDIA.dbo.[USER] B
52 WHERE A.[USER_ID] = B.[USER_ID];
53 GO
54
55 -- 为视图建立插入触发器
56 CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
57 INSTEAD OF INSERT
58
59 AS
60
61 BEGIN TRANSACTION
62 INSERT INTO MEDIA.dbo.[USER]
63 SELECT [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
64 INSERT INTO [RENHU\B].MEDIA.dbo.[USER]
65 SELECT scope_identity(), [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
66 IF ( @@ERROR <> 0 )
67 BEGIN
68 ROLLBACK TRANSACTION
69 END
70 ELSE
71 COMMIT TRANSACTION
72
73 GO
74
75 -- 为视图建立更新触发器
76 CREATE TRIGGER [TRG_UPDATE_USER] on [USER_VIEW]
77 INSTEAD OF UPDATE
78 AS
79 BEGIN TRANSACTION
80 IF UPDATE([USER_ID])
81 BEGIN
82 RAISERROR(N'USER_ID列不能被更新', 10, 1);
83 ROLLBACK TRANSACTION
84 RETURN
85 END
86
87 IF UPDATE([USER_NAME])
88 BEGIN
89 RAISERROR(N'USER_NAME列不能被更新', 10, 1);
90 ROLLBACK TRANSACTION
91 RETURN
92 END
93
94 IF UPDATE([USER_PWD])
95 BEGIN
96 UPDATE [USER] SET [USER_PWD] = Inserted.[USER_PWD] FROM [USER] JOIN Inserted
97 ON [USER].[USER_ID] = Inserted.[USER_ID];
98 END
99
100 IF UPDATE([USER_ROLE])
101 BEGIN
102 UPDATE [USER] SET [USER_ROLE] = Inserted.[USER_ROLE] FROM [USER] JOIN Inserted
103 ON [USER].[USER_ID] = Inserted.[USER_ID];
104 END
105
106 IF UPDATE([USER_IM])
107 BEGIN
108 UPDATE A SET [USER_IM] = Inserted.[USER_IM] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
109 ON A.[USER_ID] = Inserted.[USER_ID];
110 END
111
112 IF UPDATE([USER_AGE])
113 BEGIN
114 UPDATE A SET [USER_AGE] = Inserted.[USER_AGE] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
115 ON A.[USER_ID] = Inserted.[USER_ID];
116 END
117
118 IF UPDATE([USER_GENDER])
119 BEGIN
120 UPDATE A SET [USER_GENDER] = Inserted.[USER_GENDER] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
121 ON A.[USER_ID] = Inserted.[USER_ID];
122 END
123
124 IF UPDATE([USER_DES])
125 BEGIN
126 UPDATE A SET [USER_DES] = Inserted.[USER_DES] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
127 ON A.[USER_ID] = Inserted.[USER_ID];
128 END
129
130 COMMIT TRANSACTION
131
132 --insert into [USER_VIEW]([USER_NAME],[USER_PWD],[USER_ROLE],[USER_IM],[USER_AGE],[USER_GENDER],[USER_DES]) values('h',12,0,'123456',22,0,'I am ');
133 --
134 --update [USER_VIEW] SET [USER_IM] = '45698', [USER_PWD] = 2356, [USER_ROLE]=10 where [USER_ID] = 3;
1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 EXECUTE sp_serveroption @server='RENHU\A',@optname='lazy schema validation',@optvalue='true'
9 GO
10
11 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
12 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
13 GO
14
15 CREATE DATABASE MEDIA;
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到B
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\B')
23 EXECUTE sp_addlinkedserver 'RENHU\B', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\B','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY IDENTITY(1,1),
31 [USER_NAME] NVARCHAR(20) NOT NULL,
32 [USER_PWD] VARBINARY NOT NULL,
33 [USER_ROLE] INT NOT NULL); -- 类别,0代表管理员,1代表普通用户
34 GO
35
36 -- 建立用户
37 CREATE USER XQLS FROM LOGIN xqls;
38 GO
39
40 -- 分配权限
41 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
42 GO
43
44 -- 建立视图
45 CREATE VIEW [USER_VIEW]
46 AS
47 SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
48 A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
49 B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
50 B.[USER_GENDER] AS [USER_GENDER], B.[USER_DES] AS [USER_DES]
51 FROM MEDIA.dbo.[USER] A, [RENHU\B].MEDIA.dbo.[USER] B
52 WHERE A.[USER_ID] = B.[USER_ID];
53 GO
54
55 -- 为视图建立插入触发器
56 CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
57 INSTEAD OF INSERT
58
59 AS
60
61 BEGIN TRANSACTION
62 INSERT INTO MEDIA.dbo.[USER]
63 SELECT [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
64 INSERT INTO [RENHU\B].MEDIA.dbo.[USER]
65 SELECT scope_identity(), [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
66 IF ( @@ERROR <> 0 )
67 BEGIN
68 ROLLBACK TRANSACTION
69 END
70 ELSE
71 COMMIT TRANSACTION
72
73 GO
74
75 -- 为视图建立更新触发器
76 CREATE TRIGGER [TRG_UPDATE_USER] on [USER_VIEW]
77 INSTEAD OF UPDATE
78 AS
79 BEGIN TRANSACTION
80 IF UPDATE([USER_ID])
81 BEGIN
82 RAISERROR(N'USER_ID列不能被更新', 10, 1);
83 ROLLBACK TRANSACTION
84 RETURN
85 END
86
87 IF UPDATE([USER_NAME])
88 BEGIN
89 RAISERROR(N'USER_NAME列不能被更新', 10, 1);
90 ROLLBACK TRANSACTION
91 RETURN
92 END
93
94 IF UPDATE([USER_PWD])
95 BEGIN
96 UPDATE [USER] SET [USER_PWD] = Inserted.[USER_PWD] FROM [USER] JOIN Inserted
97 ON [USER].[USER_ID] = Inserted.[USER_ID];
98 END
99
100 IF UPDATE([USER_ROLE])
101 BEGIN
102 UPDATE [USER] SET [USER_ROLE] = Inserted.[USER_ROLE] FROM [USER] JOIN Inserted
103 ON [USER].[USER_ID] = Inserted.[USER_ID];
104 END
105
106 IF UPDATE([USER_IM])
107 BEGIN
108 UPDATE A SET [USER_IM] = Inserted.[USER_IM] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
109 ON A.[USER_ID] = Inserted.[USER_ID];
110 END
111
112 IF UPDATE([USER_AGE])
113 BEGIN
114 UPDATE A SET [USER_AGE] = Inserted.[USER_AGE] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
115 ON A.[USER_ID] = Inserted.[USER_ID];
116 END
117
118 IF UPDATE([USER_GENDER])
119 BEGIN
120 UPDATE A SET [USER_GENDER] = Inserted.[USER_GENDER] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
121 ON A.[USER_ID] = Inserted.[USER_ID];
122 END
123
124 IF UPDATE([USER_DES])
125 BEGIN
126 UPDATE A SET [USER_DES] = Inserted.[USER_DES] FROM [RENHU\B].MEDIA.dbo.[USER] A JOIN Inserted
127 ON A.[USER_ID] = Inserted.[USER_ID];
128 END
129
130 COMMIT TRANSACTION
131
132 --insert into [USER_VIEW]([USER_NAME],[USER_PWD],[USER_ROLE],[USER_IM],[USER_AGE],[USER_GENDER],[USER_DES]) values('h',12,0,'123456',22,0,'I am ');
133 --
134 --update [USER_VIEW] SET [USER_IM] = '45698', [USER_PWD] = 2356, [USER_ROLE]=10 where [USER_ID] = 3;
B:
Code
1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 CREATE DATABASE MEDIA;
9 GO
10
11 EXECUTE sp_serveroption @server='RENHU\B',@optname='lazy schema validation',@optvalue='true'
12 GO
13
14 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
15 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到A
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\A')
23 EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY,
31 [USER_IM] NVARCHAR(50), -- QQ/MSN
32 [USER_AGE] INT, -- 年龄
33 [USER_GENDER] BIT, -- 性别
34 [USER_DES] NTEXT) -- 个人简介
35 GO
36
37 -- 建立用户
38 CREATE USER XQLS FROM LOGIN xqls;
39 GO
40
41 -- 分配权限
42 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
43 GO
44
45 ---- 建立视图
46 --CREATE VIEW [USER_VIEW]
47 --AS
48 -- SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
49 -- A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
50 -- B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
51 -- B.[USER_GENDER] AS [USER_GENDER],B.[USER_DES] AS [USER_DES]
52 -- FROM [RENHU\A].MEDIA.dbo.[USER] A, MEDIA.dbo.[USER] B
53 -- WHERE A.[USER_ID] = B.[USER_ID];
54 --GO
55 --
56 ---- 为视图建立插入触发器
57 --CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
58 --INSTEAD OF INSERT
59 --
60 --AS
61 --
62 --BEGIN TRANSACTION
63 -- INSERT INTO MEDIA.dbo.[USER]
64 -- SELECT [USER_ID], [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
65 -- INSERT INTO [RENHU\A].MEDIA.dbo.[USER]
66 -- SELECT [USER_ID], [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
67 --IF ( @@ERROR <> 0 )
68 --BEGIN
69 -- ROLLBACK TRANSACTION
70 --END
71 --ELSE
72 -- COMMIT TRANSACTION
73 --GO
1 USE MASTER;
2 GO
3
4 IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='MEDIA')
5 DROP DATABASE MEDIA;
6 GO
7
8 CREATE DATABASE MEDIA;
9 GO
10
11 EXECUTE sp_serveroption @server='RENHU\B',@optname='lazy schema validation',@optvalue='true'
12 GO
13
14 IF NOT EXISTS(SELECT 1 FROM MASTER..SYSLOGINS WHERE LOGINNAME='xqls')
15 CREATE LOGIN xqls WITH PASSWORD = 'wisdom317'
16 GO
17
18 USE MEDIA;
19 GO
20
21 -- 连接到A
22 IF NOT EXISTS(SELECT 1 FROM sys.servers srv WHERE srv.name = 'RENHU\A')
23 EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
24 GO
25
26 EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
27 GO
28
29 CREATE TABLE [USER](
30 [USER_ID] INT PRIMARY KEY,
31 [USER_IM] NVARCHAR(50), -- QQ/MSN
32 [USER_AGE] INT, -- 年龄
33 [USER_GENDER] BIT, -- 性别
34 [USER_DES] NTEXT) -- 个人简介
35 GO
36
37 -- 建立用户
38 CREATE USER XQLS FROM LOGIN xqls;
39 GO
40
41 -- 分配权限
42 GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON [USER] TO xqls;
43 GO
44
45 ---- 建立视图
46 --CREATE VIEW [USER_VIEW]
47 --AS
48 -- SELECT A.[USER_ID] AS [USER_ID], A.[USER_NAME] AS [USER_NAME],
49 -- A.[USER_PWD] AS [USER_PWD], A.[USER_ROLE] AS [USER_ROLE],
50 -- B.[USER_IM] AS [USER_IM], B.[USER_AGE] AS [USER_AGE],
51 -- B.[USER_GENDER] AS [USER_GENDER],B.[USER_DES] AS [USER_DES]
52 -- FROM [RENHU\A].MEDIA.dbo.[USER] A, MEDIA.dbo.[USER] B
53 -- WHERE A.[USER_ID] = B.[USER_ID];
54 --GO
55 --
56 ---- 为视图建立插入触发器
57 --CREATE TRIGGER [TRG_INS_USER] on [USER_VIEW]
58 --INSTEAD OF INSERT
59 --
60 --AS
61 --
62 --BEGIN TRANSACTION
63 -- INSERT INTO MEDIA.dbo.[USER]
64 -- SELECT [USER_ID], [USER_IM], [USER_AGE], [USER_GENDER], [USER_DES] FROM inserted;
65 -- INSERT INTO [RENHU\A].MEDIA.dbo.[USER]
66 -- SELECT [USER_ID], [USER_NAME], [USER_PWD], [USER_ROLE] FROM inserted;
67 --IF ( @@ERROR <> 0 )
68 --BEGIN
69 -- ROLLBACK TRANSACTION
70 --END
71 --ELSE
72 -- COMMIT TRANSACTION
73 --GO
本文基于署名 2.5 中国大陆许可协议发布,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名小橋流水(包含链接)。如您有任何疑问或者授权方面的协商,请给我发邮件。