Ответ 1
База данных- > Задачи- > Сгенерировать скрипты → Далее → Далее
В Выберите Script Параметры пользовательский интерфейс в разделе "Параметры таблицы/просмотра", установите Script Триггеры на True.
Как сгенерировать скрипты для всех триггеров в базе данных с помощью Microsoft SQL Server Management Studio
Я хотел бы создать SQL Script, который содержит SQL, чтобы создать все триггеры, существующие в нашей базе данных. Триггеры были добавлены непосредственно через панель запросов SSMS, поэтому в настоящее время нет источника, кроме триггера в самой базе данных.
Я уже пробовал метод, в котором вы щелкните правой кнопкой мыши базу данных, выберите Tasks->Generate Scripts и используйте параметр "Script Целая база данных и все объекты". Хотя это создает SQL Script для таблиц и ограничений, он не генерирует SQL для триггеров.
Я также понимаю, что я могу щелкнуть правой кнопкой мыши по каждому триггеру в базе данных и выбрать опцию Generate SQL Script, но в настоящее время существует 46 таблиц под аудитом (для вставки, обновления и удаления).
Скорее вручную создайте триггер для вставки, обновления и удаления Script для каждой из 46 таблиц, есть ли более простой способ сделать это? Или, если я начну нажимать, копировать и вставлять?
Ответы
Ответ 2
Я знаю, что ответ уже принят, но хочу предоставить другое решение для случаев, когда по какой-то причине мастер SSMS не может генерировать script для триггеров (в моем случае это был MSSQL2008R2)
Это решение основано на идее dana выше, но вместо этого использует 'sql_modules', чтобы предоставить полный код триггера если оно превышает 4000 символов (ограничение столбца "текст" представления "syscomments" )
select [definition],'GO' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR'
Щелкните правой кнопкой мыши по сетке результатов, а затем "Сохранить результаты как..." сохранит файл с сохраненным форматированием.
Ответ 3
Как насчет этого?
select text from syscomments where text like '%CREATE TRIGGER%'
EDIT - за jj комментарий ниже, syscomments устарел и будет удален в будущем. Используйте перечисленные выше решения на основе мастера или script:)
Ответ 4
В script все триггеры вы можете определить хранимую процедуру:
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
-- Procedure:
-- [dbo].[Createscriptofalltriggers]
--
-- Parameter:
-- @InclDrop bit
-- possible values:
-- 0 - Script to drop the triggers is not generated.
-- 1 - Script to drip the triggers is generated.
ALTER PROCEDURE [dbo].[Createscriptofalltriggers]
@InclDrop BIT =1
AS
DECLARE @SQL VARCHAR(8000),
@Text NVARCHAR(4000),
@BlankSpaceAdded INT,
@BasePos INT,
@CurrentPos INT,
@TextLength INT,
@LineId INT,
@MaxID INT,
@AddOnLen INT,
@LFCR INT,
@DefinedLength INT,
@SyscomText NVARCHAR(4000),
@Line NVARCHAR(1000),
@UserName SYSNAME,
@ObjID INT,
@OldTrigID INT
SET nocount ON
SET @DefinedLength = 1000
SET @BlankSpaceAdded = 0
IF @InclDrop <> 0
SET @InclDrop =1
-- This Part Validated the Input parameters
DECLARE @Triggers TABLE
(
username SYSNAME NOT NULL,
trigname SYSNAME NOT NULL,
objid INT NOT NULL
)
DECLARE @TrigText TABLE
(
objid INT NOT NULL,
lineid INT NOT NULL,
linetext NVARCHAR(1000) NULL
)
INSERT INTO @Triggers
(username,
trigname,
objid)
SELECT DISTINCT A.NAME,
B.NAME,
B.id
FROM dbo.sysusers A,
dbo.sysobjects B,
dbo.syscomments C
WHERE A.uid = B.uid
AND B.type = 'Tr'
AND B.id = C.id
AND C.encrypted = 0
IF EXISTS(SELECT C.*
FROM syscomments C,
sysobjects O
WHERE O.id = C.id
AND O.type = 'Tr'
AND C.encrypted = 1)
BEGIN
PRINT '/*'
PRINT 'The following encrypted triggers were found'
PRINT 'The procedure could not write the script for it'
SELECT DISTINCT A.NAME,
B.NAME,
B.id
FROM dbo.sysusers A,
dbo.sysobjects B,
dbo.syscomments C
WHERE A.uid = B.uid
AND B.type = 'Tr'
AND B.id = C.id
AND C.encrypted = 1
PRINT '*/'
END
DECLARE ms_crs_syscom CURSOR local forward_only FOR
SELECT T.objid,
C.text
FROM @Triggers T,
dbo.syscomments C
WHERE T.objid = C.id
ORDER BY T.objid,
C.colid
FOR READ only
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
SET @OldTrigID = -1
FETCH next FROM ms_crs_syscom INTO @ObjID, @SyscomText
WHILE @@fetch_status = 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = Len(@SyscomText)
IF @ObjID <> @OldTrigID
BEGIN
SET @LineID = 1
SET @OldTrigID = @ObjID
END
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = Charindex(Char(13) + Char(10), @SyscomText,
@BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
WHILE ( Isnull(Len(@Line), 0) + @BlankSpaceAdded
+ @CurrentPos - @BasePos + @LFCR ) >
@DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (
Isnull(Len(@Line),
0
) +
@BlankSpaceAdded )
INSERT @TrigText
VALUES ( @ObjID,
@LineId,
Isnull(@Line, N'')
+ Isnull(Substring(@SyscomText, @BasePos,
@AddOnLen),
N''))
SELECT @Line = NULL,
@LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen,
@BlankSpaceAdded = 0
END
SELECT @Line = Isnull(@Line, N'')
+ Isnull(Substring(@SyscomText, @BasePos,
@CurrentPos
[email protected] +
@LFCR),
N'')
SELECT @BasePos = @CurrentPos + 2
INSERT @TrigText
VALUES( @ObjID,
@LineId,
@Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
WHILE ( Isnull(Len(@Line), 0) + @BlankSpaceAdded
+ @TextLength - @BasePos + 1 ) >
@DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (
Isnull(Len(@Line),
0
) +
@BlankSpaceAdded )
INSERT @TrigText
VALUES ( @ObjID,
@LineId,
Isnull(@Line, N'')
+ Isnull(Substring(@SyscomText,
@BasePos,
@AddOnLen),
N''))
SELECT @Line = NULL,
@LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen,
@BlankSpaceAdded = 0
END
SELECT @Line = Isnull(@Line, N'')
+ Isnull(Substring(@SyscomText,
@BasePos,
@TextLength
[email protected]+1
), N'')
IF Len(@Line) < @DefinedLength
AND Charindex(' ', @SyscomText, @TextLength + 1)
> 0
BEGIN
SELECT @Line = @Line + ' ',
@BlankSpaceAdded = 1
END
END
END
END
FETCH next FROM ms_crs_syscom INTO @ObjID, @SyscomText
END
IF @Line IS NOT NULL
INSERT @TrigText
VALUES( @ObjID,
@LineId,
@Line )
CLOSE ms_crs_syscom
PRINT '-- You should run this result under dbo if your triggers belong to multiple users'
PRINT ''
IF @InclDrop = 1
BEGIN
PRINT '-- Dropping the Triggers'
PRINT ''
SELECT 'If exists(Select * from sysObjects where id =Object_ID(''['
+ username + '].[' + trigname
+ ']'') and ObjectProperty(Object_ID(''['
+ username + '].[' + trigname + ']''), ''ISTRIGGER'')=1) Drop Trigger ['
+ username + '].[' + trigname + '] ' + Char(13)
+ Char(10) + 'GO' + Char(13) + Char(10) + Char(13)
+ Char(10)
FROM @Triggers
END
PRINT '----------------------------------------------'
PRINT '-- Creation of Triggers'
PRINT ''
PRINT ''
DECLARE ms_users CURSOR local forward_only FOR
SELECT T.username,
T.objid,
Max(D.lineid)
FROM @Triggers T,
@TrigText D
WHERE T.objid = D.objid
GROUP BY T.username,
T.objid
FOR READ only
OPEN ms_users
FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID
WHILE @@fetch_status = 0
BEGIN
PRINT 'SetUser N''' + @UserName + '''' + Char(13)
+ Char(10)
SELECT '-- Text of the Trigger'= CASE lineid
WHEN 1 THEN 'GO' + Char(13) + Char(
10)
+
linetext
WHEN @MaxID THEN linetext + 'GO'
ELSE linetext
END
FROM @TrigText
WHERE objid = @ObjID
ORDER BY lineid
PRINT 'Setuser'
FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID
END
CLOSE ms_users
PRINT 'GO'
PRINT '------End ------'
DEALLOCATE ms_crs_syscom
DEALLOCATE ms_users
GO
Как выполнить его:
SET nocount ON
DECLARE @return_value INT
EXEC @return_value = [dbo].[Createscriptofalltriggers] @InclDrop = 1
SELECT 'Return Value' = @return_value
ГО
Ответ 5
Использование syscomments может не всегда работать. Текстовый столбец syscomments ограничен 4000 символами. Триггер больше этого будет усечен. Возможно, вы не сможете полностью просмотреть его.
Ответ 6
Собственно, если вы посмотрите на таблицу sys.comments, у нее есть поле colid, которое является числовым столбцом; если оно больше максимального, оно будет иметь число > 1. Если вы должны выбрать один s > 1 colid и скопировать оба (если было 2 записи), это даст вам весь триггер!