USE [master]
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name='Temp') DROP DATABASE Temp
GO
CREATE DATABASE Temp ON
(
NAME='Temp',
FILENAME='D:\Temp.mdf'
)
GO
USE [Temp]
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='Types' AND xtype='U') DROP TABLE [Types]
GO
CREATE TABLE [Types]
(
[Id] INT NOT NULL IDENTITY,
[Name] VARCHAR(32) NOT NULL,
[ParentId] INT NULL, --父类的ID
[Order] INT NOT NULL
)
GO
/*
ID ParentId ORDER
1 NULL 0
2 NULL 1
1-1 1 0
1-2 2 1
1-3 1 1
2-1 2 0
2-2 2 1
2-3 2 2
1、将被修改的类的同类,并且序号比它的,前移一位
2、将被修改的分类的序号,修改为目标父类的最后一位
*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='UP_U_Types' AND xtype='P') DROP PROC UP_U_Types
GO
CREATE PROC UP_U_Types
@Id INT,
@Name VARCHAR(32)=NULL,
@ParentId INT=NULL,
@Order INT=NULL
AS
BEGIN
IF @Name IS NOT NULL
UPDATE [Types] SET [Name]=@Name WHERE [Id]=Id
IF @ParentId IS NOT NULL OR @Order IS NOT NULL
BEGIN
DECLARE @OldParentId INT,@OldOrder INT
SELECT @OldParentId=[ParentId],@OldOrder=[Order] FROM [Types] WHERE [Id]=@Id
IF @ParentId IS NOT NULL
BEGIN
DECLARE @Count INT
SELECT @Count=COUNT(*) FROM [Types] WHERE [ParentId]=@ParentId
UPDATE [Types] SET
[Order]=
CASE WHEN [Id]=@Id
THEN @Count
ELSE [Order]-1
END,
[ParentId]=
CASE WHEN [Id]=@Id
THEN @ParentId
ELSE [ParentId]
END
WHERE [Id]=@Id OR ((@OldParentId IS NULL AND [ParentId] IS NULL) OR [ParentId]=@OldParentId AND [Order]>@OldOrder)
SET @OldParentId=@ParentId
SET @OldOrder=@Count
END
IF @Order IS NOT NULL
BEGIN
UPDATE [Types] SET [Order]=
CASE WHEN [Id]=@Id
THEN @Order
WHEN @Order>@OldOrder
THEN [Order]-1
ELSE [Order]+1
END
WHERE ([ParentId]=@OldParentId OR @OldParentId IS NULL AND [ParentId] IS NULL) AND
(
[Id]=@Id OR
(@Order>@OldOrder AND [Order]>@OldOrder AND [Order]<=@Order) OR --从上向下移动
(@Order<@OldOrder AND [Order]<@OldOrder AND [Order]>=@Order)--从下向上移动
)
END
END
END
GO
SELECT * FROM [Types]
EXEC UP_U_Types 14,NULL,1,3
IF EXISTS(SELECT * FROM sysobjects WHERE name='UP_U_Types_Order' AND xtype='P') DROP PROC UP_U_Types_Order
GO
CREATE PROC UP_U_Types_Order
@Id INT,
@Order INT
AS
BEGIN
DECLARE @OldParentId INT,@OldOrder INT
SELECT @OldParentId=[ParentId],@OldOrder=[Order] FROM [Types] WHERE [Id]=@Id
UPDATE [Types] SET [Order]=
CASE WHEN [Id]=@Id
THEN @Order
WHEN @Order>@OldOrder
THEN [Order]-1
ELSE [Order]+1
END
WHERE ([ParentId]=@OldParentId OR @OldParentId IS NULL AND [ParentId] IS NULL) AND
(
[Id]=@Id OR
(@Order>@OldOrder AND [Order]>@OldOrder AND [Order]<=@Order) OR --从上向下移动
(@Order<@OldOrder AND [Order]<@OldOrder AND [Order]>=@Order)--从下向上移动
)
END
--SELECT * FROM [Types]
--EXEC UP_U_Types_Order 5,4
--EXEC UP_U_Types_Order 11,2
IF EXISTS(SELECT * FROM sysobjects WHERE name='UP_I_Types' AND xtype='P') DROP PROC UP_I_Types
GO
CREATE PROC UP_I_Types
@Name VARCHAR(32),
@ParentId INT=NULL
AS
BEGIN
INSERT [Types]([Name],[ParentId],[Order])VALUES(@Name,@ParentId,(SELECT COUNT(*) FROM [Types] WHERE [ParentId]=@ParentId OR (@ParentId IS NULL AND [ParentId] IS NULL)))
END
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='UP_D_Types' AND xtype='P') DROP PROC UP_D_Types
GO
CREATE PROC UP_D_Types
@Id INT
AS
BEGIN
DECLARE @ParentId INT,@Order INT --准备好接收被删除记录的父类ID以及原有序号的变量
SELECT @ParentId=[ParentId],@Order=[Order] FROM [Types] WHERE [Id]=@Id --为这两个变量查询并赋值
--修改:将当前要删除的记录的序号,移到最后一位
--其他当前组,并且序号比它大的记录,序号-1
UPDATE [Types] SET [Order]=
CASE WHEN [Id]=@Id
THEN (SELECT COUNT(*) FROM [Types] WHERE ([ParentId]=@ParentId OR (@ParentId IS NULL AND [ParentId] IS NULL)))-1
ELSE [Order]-1
END
WHERE [Id]=@Id OR (([ParentId]=@ParentId OR (@ParentId IS NULL AND [ParentId] IS NULL)) AND [Order]>@Order)
DELETE [Types] WHERE [Id]=@Id
END
GO
--EXEC UP_D_Types 2
/*
1 0
2 1
3 4 ID=3 4
4 2 ORDER>3 ORDER-1
5 3
1 0
2 1
3 4
4 2
5 3
*/
ALTER TABLE [Types] ADD
CONSTRAINT PK_Types_Id PRIMARY KEY([Id]),
CONSTRAINT UQ_Types_Order UNIQUE([ParentId],[Order]),
CONSTRAINT CK_Types_Order CHECK([Order]>=0),
CONSTRAINT FK_Types_ParentId FOREIGN KEY([ParentId]) REFERENCES [Types]([Id])
GO
--触发器:在执行增、删、改时,触发的事件
IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_IDU_Types' AND xtype='TR') DROP TRIGGER TR_IDU_Types
GO
CREATE TRIGGER TR_IDU_Types ON [Types]
FOR INSERT,DELETE,UPDATE AS
BEGIN
IF EXISTS(SELECT * FROM [Types] GROUP BY [ParentId] HAVING MAX([Order])>=COUNT(*))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('序号超出范围',11,1)
END
END
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='TR_IU_Types' AND xtype='TR') DROP TRIGGER TR_IU_Types
GO
CREATE TRIGGER TR_IU_Types ON [Types]
FOR INSERT,UPDATE AS
BEGIN
DECLARE @Id INT,@ParentId INT
--修改或添加时,有可能同时修改多条记录
DECLARE C CURSOR FOR SELECT [Id],[ParentId] FROM [INSERTED]
OPEN C
FETCH NEXT FROM C INTO @Id,@ParentId
WHILE @@FETCH_STATUS=0
BEGIN
WHILE @ParentId IS NOT NULL
BEGIN
SELECT @ParentId=[ParentId] FROM [Types] WHERE [Id]=@ParentId
IF @ParentId=@Id
BEGIN
ROLLBACK TRANSACTION
CLOSE C
DEALLOCATE C
RAISERROR('隶属关系发生递归!',11,1)
RETURN
END
END
FETCH NEXT FROM C INTO @Id,@ParentId
END
CLOSE C
DEALLOCATE C
END
GO
EXEC UP_I_Types 'A'
EXEC UP_I_Types 'B'
EXEC UP_I_Types 'C'
EXEC UP_I_Types 'A-1',1
EXEC UP_I_Types 'A-2',1
EXEC UP_I_Types 'A-3',1
EXEC UP_I_Types 'A-4',1
EXEC UP_I_Types 'A-5',1
EXEC UP_I_Types 'A-6',1
EXEC UP_I_Types 'A-7',1
EXEC UP_I_Types 'A-8',1
EXEC UP_I_Types 'A-9',1
EXEC UP_I_Types 'B-1',2
EXEC UP_I_Types 'B-2',2
EXEC UP_I_Types 'B-3',2
SQL Server 子父级类别表(笔记)
最新推荐文章于 2024-03-28 16:24:22 发布