42 lines
1.4 KiB
MySQL
42 lines
1.4 KiB
MySQL
|
CREATE OR ALTER TRIGGER E_DDL_CREATE_TABLE
|
|||
|
|
ON DATABASE
|
|||
|
|
FOR CREATE_TABLE
|
|||
|
|
AS
|
|||
|
|
BEGIN
|
|||
|
|
DECLARE @XML XML = EVENTDATA();
|
|||
|
|
-- contr<74>le du nom
|
|||
|
|
IF @XML.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') NOT LIKE 'T?_%' ESCAPE('?')
|
|||
|
|
OR @XML.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') NOT LIKE '%_[A-Z][A-Z][A-Z]' ESCAPE('?')
|
|||
|
|
BEGIN
|
|||
|
|
ROLLBACK;
|
|||
|
|
THROW 66666, 'Le nom d''une table doit <20>tre pr<70>fix<69> par "T_" et suffix<69> par un trigramme, par exemple "_ABC"', 1;
|
|||
|
|
END;
|
|||
|
|
-- utilisation de types obsol<6F>tes
|
|||
|
|
IF EXISTS(SELECT *
|
|||
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|||
|
|
WHERE TABLE_NAME = @XML.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
|
|||
|
|
AND TABLE_SCHEMA = @XML.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')
|
|||
|
|
AND DATA_TYPE IN ('text', 'ntext', 'image', 'datetime', 'smalldatetime'))
|
|||
|
|
BEGIN
|
|||
|
|
ROLLBACK;
|
|||
|
|
THROW 66666, 'les colonnes d''une table ne doivent pas comporter de type de donn<6E>es obsol<6F>tes ("text", "ntext", "image", "datetime", "smalldatetime").', 1;
|
|||
|
|
END;
|
|||
|
|
END;
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
CREATE TRIGGER E_DDL_CREATE_TABLE
|
|||
|
|
ON ALL SERVER
|
|||
|
|
FOR CREATE_DATABASE
|
|||
|
|
AS
|
|||
|
|
BEGIN
|
|||
|
|
DECLARE @XML XML = EVENTDATA();
|
|||
|
|
IF @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname') NOT LIKE 'DB?_%' ESCAPE '?'
|
|||
|
|
BEGIN
|
|||
|
|
ROLLBACK;
|
|||
|
|
THROW 66666, 'Le nom d''une base de donn<6E>es doit <20>tre pr<70>fix<69> par "DB_".', 1;
|
|||
|
|
END;
|
|||
|
|
END;
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
|