在SQL Server数据库中批量修改触发器的方法

Question

我有一个几乎所有表都使用Insert、Update和Delete触发器的数据库。它们将执行该操作的主机和程序记录在单独的审核表中。所有触发器都包含以下select语句，用于设置插入到审计表中的变量：

  select @HostName = HostName, @ProgramName = Program_Name 
    from master..sysprocesses where SPID = @@SPID

我们现在希望迁移到Azure SQL数据库，它不支持master..sysprocesses语法。表似乎也被弃用了：https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-ver15

我们需要做的是更新触发器以使用下面的代码：

  select @HostName = [host_name], @ProgramName = [program_name]
    from sys.dm_exec_sessions where session_id = @@SPID

但是，数据库有数百个表，并且每个表都有三个需要更新的触发器。每个触发器的文本替换是相同的。有没有一种可行的方法来编写脚本，在数据库中的所有触发器上执行此更新？

Answer 1

好吧，我只是通过在几个触发器中插入字符串(当然是作为注释)然后运行它来测试这一点。我并不主张这样做才是正确的方法，因为这个链接将帮助您正确地执行动态sql https://dba.stackexchange.com/questions/165149/exec-vs-sp-executesql-performance。

然而，这确实是有效的，并将帮助您理解如何将这些东西拼凑在一起以达到这一点。

注意，触发器之间的任何格式差异都可能导致遗漏一些，因此您需要自己验证0。

DECLARE @string VARCHAR(8000)='select @HostName = HostName, @ProgramName = Program_Name 
from master..sysprocesses where SPID = @@SPID'
, @counter INT=1
, @Max INT
, @Sql VARCHAR(mAX)
;

IF OBJECT_ID('TempDB..#TrigUpdate') IS NOT NULL DROP TABLE #TrigUpdate;

CREATE TABLE #TrigUpdate
    (
        SqlVar VARCHAR(MAX)
        , RowID INT
    )
;

INSERT INTO #TrigUpdate
SELECT REPLACE(REPLACE(t.definition, @string, ''), 'CREATE TRIGGER', 'ALTER TRIGGER')
    , Row_Number() OVER (ORDER BY t.Definition ASC) AS RowID
FROM sys.objects o
INNER JOIN sys.sql_modules t on o.object_id =t.object_id
WHERE o.type_desc='SQL_TRIGGER'
AND CHARINDEX(@string, t.definition,1)>0
;

SET @Max = (SELECT COUNT(*) FROM #TrigUpdate);

WHILE @Counter<=@Max
    BEGIN
        SET @sql = (SELECT SqlVar FROM #TrigUpdate WHERE RowID=@counter);
        EXEC(@Sql);
        SET @Counter=@Counter+1;
    END

Answer 2

这可以用Object_Definition和Replace来完成。

Create Table #Triggers_new (TriggerName sysname, QueryText VarChar(max))

Declare @string_pattern VarChar(max), @string_replacement VarChar(max) 

Select @string_pattern = '<string_pattern>'
Select @string_replacement = '<string_replacement>'

Insert Into #Triggers_new (TriggerName, QueryText)
Select [name], Replace(Object_Definition(object_id), @string_pattern, @string_replacement)
From sys.objects
Where [type] = 'TR'
Order by [name]

-- Update #Triggers_new Set QueryText = Replace(QueryText, 'Create Trigger ', 'Alter Trigger ')

Answer 3

为什么要在系统表/视图上使用如此繁重的查询，而这些查询可以不经您的同意而更改？

你不能使用像这样的metada函数来简化你吗：

SELECT HOST_NAME(), PROGRAM_NAME()...

它会给出请求的信息值吗？