1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
| CREATE TRIGGER E_CREATE_TABLE
ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON;
IF NOT (IS_ROLEMEMBER('role_allowed_to_create_table', USER) = 1)
RETURN;
DECLARE @DDL_DATA XML = EVENTDATA(),
@SCH sysname,
@TBL sysname,
@SQL NVARCHAR(max),
@FG sysname,
@ISFG bit,
@IDX INT,
@OID INT;
SELECT @SCH = @DDL_DATA.value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@TBL = @DDL_DATA.value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname');
SELECT @FG = fg.name, @IDX = i.index_id, @OID = i.object_id,
@ISFG = CASE partition_number WHEN 1 THEN 1 ELSE 0 END
FROM sys.indexes AS i
JOIN sys.objects AS o
ON i.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.allocation_units a
ON a.container_id = p.partition_id
JOIN sys.filegroups AS fg
ON a.data_space_id = fg.data_space_id
WHERE i.index_id IN (0, 1)
AND o.name = @TBL
AND s.name = @SCH;
IF @FG = 'MonFileGroup'
RETURN
IF @ISFG = 0
BEGIN
RAISERROR('la table %s.%s ne peut pas être créée sur une partition. Elle va être détruite.', 16, 1, @SCH, @TBL);
RETURN;
END
ELSE
RAISERROR('la table %s.%s ne peut pas être créée sur le groupe de fichier %s. Elle va être migré sur le groupe de fichiers adéquat.', 16, 1, @SCH, @TBL, @FG);
ROLLBACK;
IF @IDX = 0
BEGIN
-- pas d'index cluer ton déplace la table en en créant un puis en le supprimant
SET @SQL = 'ALTER TABLE [' + @SCH + '].[' + @TBL + '] ADD _UID UNIQUEIDENTIFIER DEFAULT NEWID() CONSTRAINT _PK PRIMARY KEY ON MonFileGroup';
EXEC (@SQL);
SET @SQL = 'ALTER TABLE [' + @SCH + '].[' + @TBL + '] DROP CONSTRAINT _PK, COLUMN _UID';
EXEC (@SQL);
END
ELSE
BEGIN
-- index cluster on le reconstruit à l'identique sur le filegroup de destination
WITH
idc AS (SELECT ic.object_id, index_id,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id, is_included_column
ORDER BY index_column_id) AS index_column_id,
is_included_column,
c.name, CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END AS ord
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.index_id = 1
AND ic.object_id = @OID
),
idk AS (SELECT object_id, index_id, index_column_id, 1 AS cols,
CAST('[' + name + '] ' + ord AS NVARCHAR(MAX)) AS KEY_DEF
FROM idc
WHERE is_included_column = 0
AND index_column_id = 1
UNION ALL
SELECT idc.object_id, idc.index_id, idc.index_column_id, cols + 1,
KEY_DEF + ', ' + '[' + idc.name + '] ' + ord
FROM idc
INNER JOIN idk
ON idc.object_id = idk.object_id AND
idc.index_id = idk.index_id AND
idc.index_column_id = idk.index_column_id + 1
WHERE idc.is_included_column = 0),
dfi AS (SELECT idk.*,
ROW_NUMBER() OVER(PARTITION BY idk.object_id, idk.index_id
ORDER BY cols DESC) AS N
FROM idk
)
SELECT @SQL = 'CREATE '
+ CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END
+ ' CLUSTERED '+
' INDEX [' + i.name + '] ON [' + s.name +'].[' + o.name
+'] (' + KEY_DEF +')' + ' WITH (DROP_EXISTING = ON) ON MonFileGroup;'
FROM dfi
INNER JOIN sys.indexes AS i
ON dfi.object_id = i.object_id
AND dfi.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE o."type" = 'U' AND N = 1;
EXEC (@SQL);
END;
GO |
Partager