事主的创建表是在程序里,没源码。现在想在创建的每个月的月表上添加一个触发器。
=======================================================================
话不多说上代码。
1 –by WLS 201051210
2 –网络代码有风险
3 –复制粘贴须谨慎
4
5 ———————————————————
6 USE master
7 GO
8 ———————————————————
9 CREATE DATABASE [TestDDLTrigger] ON PRIMARY
10 ( NAME = N’TestDDLTrigger’, FILENAME = N’D:\TestDDLTrigger.mdf’ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
11 LOG ON
12 ( NAME = N’TestDDLTrigger_log’, FILENAME = N’D:\TestDDLTrigger_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
13 GO
14 ———————————————————
15 USE TestDDLTrigger
16 GO
17 ———————————————————
18 CREATE TRIGGER DDLTriCreateTablebyWLS
19 ON DATABASE
20 FOR CREATE_TABLE
21 AS
22 BEGIN
23 DECLARE @TableDate NVARCHAR(6)
24 select @TableDate=convert(nvarchar(6),GETDATE(),112)
25 –SELECT @TableDate
26 DECLARE @TempSQL NVARCHAR(1000)
27 SET @TempSQL=’CREATE TRIGGER InnerTribyWLS ON T’+@TableDate+’ ‘+’after Insert as select 1’
28 –SELECT @TempSQL
29 EXEC(@TempSQL)
30 END
31 GO
32 ———————————————————
33 CREATE TABLE [T201512](SN INT)
34 GO
35 ———————————————————
36 INSERT INTO T201512 VALUES(1111)
37 GO
38 ———————————————————
39 EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’TestDDLTrigger’
40 GO
41 USE [master]
42 GO
43 ALTER DATABASE [TestDDLTrigger] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
44 GO
45 USE [master]
46 GO
47 DROP DATABASE [TestDDLTrigger]
48 GO
49 ———————————————————
=======================================================================
问题点在于要先有表后有触发器,
所以得
1 给“创建表”这个动作做一个触发器——DDL触发器,
2 在这个触发器里再创建一个触发器用于实际需求——DML触发器。