如何使用Microsoft SQL Server Management Studio为数据库中的所有触发器生成脚本-Java 学习之路

我想生成一个包含SQL的SQL脚本来创建我们数据库中存在的所有触发器 . 触发器是通过SSMS查询窗格直接添加的，因此，除了数据库本身的触发器之外，当前没有其他源 .

我已经尝试过右键单击数据库的方法，选择 Tasks->Generate Scripts 并使用"Script Entire Database and All Objects"选项 . 虽然这确实为表和约束创建了一个SQL脚本，但它不会为触发器生成SQL .

我也明白我可以右键单击数据库中的每个触发器并选择Generate SQL Script选项，但是当前有46个表在审计中（For Insert，Update和Delete） .

而是为46个表中的每个表手动生成插入，更新和删除触发器脚本，是否有更简单的方法来执行此操作？或者，我应该开始点击，复制和粘贴？

6 回答

43

Database-> Tasks-> Generate Scripts -> Next -> Next

在 Choose Script Options UI上，在表/视图选项 Headers 下，设置 Script Triggers to True .

回复于 2024-04-27T04:35:45+08:00
1
我知道答案已被接受，但是想要提供另一种解决方案，因为某些原因SSMS向导无法为触发器生成脚本（在我的例子中是MSSQL2008R2）

此解决方案基于上述dana的想法，但如果超过4000个字符（'syscomments'视图的'text'列的限制），则使用'sql_modules'来提供触发器的完整代码
```
select [definition],'GO' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id 
 where obj.type ='TR'
```
右键单击结果网格，然后将“将结果另存为...”保存到文件并保留格式
回复于 2024-04-27T04:35:45+08:00
70

这个怎么样？

select text from syscomments where text like '%CREATE TRIGGER%'

EDIT - 根据以下jj的评论， syscomments 已弃用，将来会被删除 . 请使用上面列出的基于向导或基于脚本的解决方案继续前进:)

回复于 2024-04-27T04:35:45+08:00
4

使用syscomments可能并不总是有效 . syscomments的文本列限制为4000个字符 . 超过该触发器的触发器将被截断 . 您可能无法完全查看它 .

回复于 2024-04-27T04:35:45+08:00

要编写所有触发器的脚本，您可以定义存储过程：

SET ANSI_NULLS ON; 
GO 
SET QUOTED_IDENTIFIER ON; 
GO 
-- Procedure:
--   [dbo].[SYS_ScriptAllTriggers]
--
-- Parameter: 
--   @ScriptMode bit   
--     possible values:
--     0 - Script ALTER only
--     1 - Script CREATE only
--     2 - Script DROP + CREATE

ALTER PROCEDURE [dbo].[SYS_ScriptAllTriggers]
    @ScriptMode int = 0
AS 
BEGIN

    DECLARE @script TABLE (script varchar(max), id int identity (1,1))

    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; 

    SET @ScriptMode = ISNULL(@ScriptMode, 0);

    -- 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 
        OBJECT_SCHEMA_NAME(B.id), B.name, B.id
    FROM 
        dbo.sysobjects B, dbo.syscomments C 
    WHERE 
        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 

        insert into @script select '/*'; 
        insert into @script select 'The following encrypted triggers were found'; 
        insert into @script select 'The procedure could not write the script for it'; 

        insert into 
            @script 
        SELECT DISTINCT 
            '[' + OBJECT_SCHEMA_NAME(B.id) + '].[' + B.name + ']' --, B.id 
        FROM   
            dbo.sysobjects B, dbo.syscomments C 
        WHERE  
            B.type = 'TR' AND B.id = C.id AND C.encrypted = 1; 

        insert into @script select '*/'; 
    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 - @BasePos + @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 - @BasePos+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; 

    insert into @script select '-- You should run this result under dbo if your triggers belong to multiple users'; 
    insert into @script select ''; 

    IF @ScriptMode = 2 
    BEGIN 

        insert into @script select '-- Dropping the Triggers'; 
        insert into @script select ''; 

        insert into @script 
        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)
        FROM   
            @Triggers; 
    END; 

    IF @ScriptMode = 0
    BEGIN   
        update 
            @TrigText 
        set 
            linetext = replace(linetext, 'CREATE TRIGGER', 'ALTER TRIGGER') 
        WHERE 
            upper(left(replace(ltrim(linetext), char(9), ''), 14)) = 'CREATE TRIGGER' 
    END

    insert into @script select '----------------------------------------------'; 
    insert into @script select '-- Creation of Triggers'; 
    insert into @script select ''; 
    insert into @script select ''; 

    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 

        insert into @script select 'setuser N''' + @UserName + '''' + CHAR(13) + CHAR(10); 

        insert into @script 
        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; 

        insert into @script select 'setuser'; 

        FETCH NEXT FROM ms_users INTO @UserName, @ObjID, @MaxID; 
    END; 

    CLOSE ms_users; 

    insert into @script select 'GO'; 
    insert into @script select '------End ------'; 

    DEALLOCATE ms_crs_syscom; 
    DEALLOCATE ms_users; 

    select script from @script order by id

END

如何执行它：

SET nocount ON 
DECLARE @return_value INT 

EXEC @return_value = [dbo].[SYS_ScriptAllTriggers] @InclDrop = 1 
SELECT 'Return Value' = @return_value

走

回复于 2024-04-27T04:35:45+08:00

0

实际上，如果你查看sys.comments表，它有一个colid字段，它是一个数字列;如果它长于最大值，它将具有> 1的数字 . 如果你选择一个> 1 colID并复制它们（如果有2个记录）它会给你整个触发器！

回复于 2024-04-27T04:35:45+08:00

如何使用Microsoft SQL Server Management Studio为数据库中的所有触发器生成脚本

6 回答

相关问题