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
|
ALTER TRIGGER [TR_DB]
ON DATABASE
FOR DDL_VIEW_EVENTS, DDL_INDEX_EVENTS, DDL_TABLE_EVENTS,
DROP_PROCEDURE, CREATE_PROCEDURE, ALTER_PROCEDURE,
DROP_TRIGGER, CREATE_TRIGGER, ALTER_TRIGGER
AS
BEGIN
SET NOCOUNT ON;
IF (DB_NAME() = 'myDB')
BEGIN
DECLARE @EventData XML;
DECLARE @SQL_Statement VARCHAR(8000);
SET @EventData = EventData();
SELECT @SQL_Statement = @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(8000)');
IF (@SQL_Statement LIKE 'CREATE TABLE%[_]%' AND @SQL_Statement NOT LIKE '%CREATE TABLE #%')
BEGIN
DECLARE @INIT_CREATE_TABLE VARCHAR(512);
SELECT @INIT_CREATE_TABLE = SUBSTRING(@SQL_Statement, 1, CHARINDEX('(', @SQL_Statement))
IF @INIT_CREATE_TABLE LIKE '%[_]%'
BEGIN
PRINT 'Is is not possible to create this table';
ROLLBACK TRANSACTION;
END;
END;
ELSE
BEGIN
INSERT MASTER.dbo.ShemaModification
SELECT
GETDATE(),
@EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(1024)'),
@SQL_Statement;
END;
END;
SET NOCOUNT OFF;
END; |
Partager