求批量插入触发器代码

[testtools]

如题
1.有上千个数据库，类型一样，表也一样
2.触发器已有，单个插入可用
3.求问如何将此触发器批量插入到上千个库中？
感谢大神！

[testtools]

触发器如下： CREATE TRIGGER [TR_Voucher_Insert] ON t_Voucher FOR INSERT AS DECLARE @Exp nvarchar(255) SELECT @Exp=FExplanation FROM INSERTED IF @Exp IS NOT NULL BEGIN DECLARE @waitTimes INT DECLARE @TaxNum nvarchar(255) DECLARE @StartDate nvarchar(200) DECLARE @accDate datetime SET NOCOUNT ON SELECT @TaxNum = CONVERT(nvarchar(255), Fvalue) FROM t_systemProfile where FCategory='HRMS' AND FKEY='CurrentYear' Select @accDate = FDate from Inserted IF @TaxNum='' BEGIN SET @TaxNum='0' END select @StartDate = startDate,@waitTimes = waitTimes from master..t_tabiz where taxNum = @TaxNum and startDate<= @accDate and stopDate >= @accDate if (@StartDate is null or @StartDate='') begin ROLLBACK TRAN RAISERROR('错误!',18,18) END END

[testtools]

本人菜鸟，看不太懂。还是感谢版主！ 数据库：2015102212300121 2015112102131020 ......... 要想在上面数据库中都插入上面的触发器，麻烦直接帮我写下吧。

[吉普赛的歌]

USE [master]
GO
IF OBJECT_ID('dbo.Proc_CreateTrigger') IS NOT NULL
DROP PROC dbo.Proc_CreateTrigger
GO
CREATE PROC Proc_CreateTrigger
@dbName NVARCHAR(200)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX),@sql2 NVARCHAR(MAX)
    SET @sql='CREATE TRIGGER dbo.[TR_Voucher_Insert]
ON t_Voucher
FOR  INSERT
AS
	DECLARE @Exp NVARCHAR(255)
	SELECT @Exp = FExplanation
	FROM   INSERTED
	
	IF @Exp IS NOT NULL
	BEGIN
	    DECLARE @waitTimes INT
	    DECLARE @TaxNum NVARCHAR(255)
	    DECLARE @StartDate NVARCHAR(200)
	    DECLARE @accDate DATETIME
	    SET NOCOUNT ON
	    SELECT @TaxNum = CONVERT(NVARCHAR(255), Fvalue)
	    FROM   t_systemProfile
	    WHERE  FCategory     = ''''HRMS''''
	           AND FKEY      = ''''CurrentYear''''
	    
	    SELECT @accDate = FDate
	    FROM   INSERTED
	    
	    IF @TaxNum = ''''''''
	    BEGIN
	        SET @TaxNum = ''''0''''
	    END
	    
	    SELECT @StartDate = startDate,
	           @waitTimes     = waitTimes
	    FROM   MASTER..t_tabiz
	    WHERE  taxNum         = @TaxNum
	           AND startDate <= @accDate
	           AND stopDate >= @accDate 
	    
	    IF (@StartDate IS NULL OR @StartDate = '''''''')
	    BEGIN
	        ROLLBACK TRAN
	        RAISERROR(''''错误!'''', 18, 18)
	    END
	END'
    SET @sql2='use ['+@dbName+'] EXEC('' '+@sql+' '') '
    
    IF OBJECT_ID(@dbName+'..t_Voucher') IS NOT NULL AND OBJECT_ID(@dbName+'.dbo.TR_Voucher_Insert') IS NULL
    BEGIN
		PRINT @sql2
		EXEC (@sql2)
    END
END
GO

EXEC sp_MSforeachdb 'if "?" like "2015%"
begin
    EXEC master.dbo.Proc_CreateTrigger ''?'' 
end
'

[日月路明]

写个游标，用动态脚本完成

[OwenZeng_DBA]

你是想把这个触发器在每个库都建上把

[吉普赛的歌]

-------- 创建测试库及测试表 ----------
USE [master]
GO
--创建测试库
IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE d.name='db1')
	DROP DATABASE db1
GO
IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE d.name='db2')
	DROP DATABASE db2
GO
CREATE DATABASE db1
GO
CREATE DATABASE db2
GO
CREATE TABLE db1.dbo.t(id INT)
CREATE TABLE db2.dbo.t(id INT)
GO
-------- 创建测试库及测试表完毕 ----------

-- 1. master 库上增加创建触发器的存储过程
-- 创建触发器脚本你自己修改……
USE [master]
GO
IF OBJECT_ID('dbo.Proc_CreateTrigger') IS NOT NULL
DROP PROC dbo.Proc_CreateTrigger
GO
CREATE PROC Proc_CreateTrigger
@dbName NVARCHAR(200)
AS
BEGIN
	DECLARE @sql NVARCHAR(MAX),@sql2 NVARCHAR(MAX)
	SET @sql='create trigger dbo.trig_t_I
on t
for insert
as
BEGIN
	select * from inserted
END'
	SET @sql2='use '+@dbName+' EXEC('' '+@sql+' '') '
	PRINT @sql2
	EXEC (@sql2)
END
GO

--遍历所有 db 开头，后面带一个数字 的库（如何选择库，你自己根据需要改一下）
--如果没有 trig_t_I 触发器的，为其加上
EXEC sp_MSforeachdb 'if "?" like "db[0-9]" and object_id(''?.dbo.trig_t_I'') is null 
begin
	EXEC master.dbo.Proc_CreateTrigger ''?'' 
end
'
USE db1
INSERT INTO t VALUES (1)

USE db2
INSERT INTO t VALUES (2)

/*
id
1

id
2
*/

[日月路明]

引用 5 楼 testtools 的回复:

本人菜鸟，看不太懂。还是感谢版主！ 数据库：2015102212300121 2015112102131020 ......... 要想在上面数据库中都插入上面的触发器，麻烦直接帮我写下吧。

懒！

[道素]

这样用sys.sp_MSforeachdb试试

EXEC sys.sp_MSforeachdb ' IF ''?'' like ''2015%'' begin  USE ?; Print ''?'';CREATE TRIGGER [TR_Voucher_Insert] ON t_Voucher 
FOR INSERT
AS
DECLARE @Exp  nvarchar(255)
SELECT @Exp=FExplanation FROM INSERTED
IF @Exp IS NOT NULL
BEGIN
       
   DECLARE @waitTimes INT
   DECLARE @TaxNum  nvarchar(255)
   DECLARE @StartDate  nvarchar(200)
   DECLARE @accDate datetime
   SET NOCOUNT ON
SELECT @TaxNum = CONVERT(nvarchar(255), Fvalue)
          FROM t_systemProfile
         where FCategory=''HRMS'' AND FKEY=''CurrentYear''
        Select @accDate = FDate from Inserted 
        IF @TaxNum=''''
BEGIN
  SET @TaxNum=''0''
END

select @StartDate = startDate,@waitTimes = waitTimes 
from master..t_tabiz
where taxNum = @TaxNum
        and startDate<= @accDate and stopDate >= @accDate 
   
if (@StartDate is null or @StartDate='''')
        begin 
ROLLBACK TRAN
        RAISERROR(''错误!'',18,18)
     
   END 

END end '

[testtools]

感谢版主，搞定了！谢谢谢谢！

[吉普赛的歌]

引用 9 楼 testtools 的回复:

感谢版主，搞定了！谢谢谢谢！

没事了就结贴吧，

[testtools]

再问一下，如果我要批量删除各数据库中的TR_Voucher_Insert这个触发器。 declare del_tr cursor for select name from sysobjects where name like 'tr_voucher_insert%' and xtype='tr' open del_tr declare @curname varchar(1000) fetch next from del_tr into @curname while(@@fetch_status=0) begin exec( 'drop trigger ' + @curname) fetch next from del_tr into @curname end close del_tr deallocate del_tr 如上，为什么不行？删不了。请教！

[吉普赛的歌]

引用 11 楼 testtools 的回复:

再问一下，如果我要批量删除各数据库中的TR_Voucher_Insert这个触发器。 如上，为什么不行？删不了。请教！

你在 #11 的代码， 只是针对 当前库的，所以没有效果。要操作， 必须遍历所有的库才是对的。 按下面的代码就好：

EXEC sp_MSforeachdb 'IF EXISTS(SELECT * FROM ?.sys.triggers WHERE NAME=''TR_Voucher_Insert'')
BEGIN
	print "?"
	use ?
	DROP TRIGGER dbo.TR_Voucher_Insert
END
'

[testtools]

报错：在此上下文中不允许使用‘AIS20061012231012’。此处只允许使用常量、表达式或变量……。

[吉普赛的歌]

稍改一下看看：

EXEC sp_MSforeachdb 'IF EXISTS(SELECT * FROM [?].sys.triggers WHERE NAME=''TR_Voucher_Insert'')
BEGIN
    print "[?]"
    use [?]
    DROP TRIGGER dbo.TR_Voucher_Insert
END
'

[testtools]

还是一样的提示。…状态1，行7。

[testtools]

…不允许使用列名。