USE [DNF_09] GO
/****** Object: DdlTrigger [tri_DDL_DATABASE_LEVEL_EVENTS] Script Date: 11/01/2011 10:17:04 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
--建立触发器 CREATE TRIGGER [tri_DDL_DATABASE_LEVEL_EVENTS] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML SET @data = EVENTDATA() declare @ObjectName nvarchar(100); set @ObjectName=@data.value( '(/EVENT_INSTANCE/ObjectName)[1] ', 'nvarchar(100) ') declare @EventType nvarchar(100); set @EventType=@data.value( '(/EVENT_INSTANCE/EventType)[1] ', 'nvarchar(100) ') --命名规范检查 declare @ReCheck nvarchar(200);set @ReCheck='' if ((left(@ObjectName,4)<>'Dnft' and left(@ObjectName,8)<>'Tmp_Dnft') and @EventType='CREATE_TABLE') set @ReCheck=' 表的命名必段以[Dnft_]开头' if ((left(@ObjectName,4)<>'Dnfv' and left(@ObjectName,8)<>'Tmp_Dnfv') and @EventType='CREATE_VIEW') set @ReCheck=' 视图的命名必段以[Dnfv_]开头' if ((left(@ObjectName,4)<>'Dnff' and left(@ObjectName,8)<>'Tmp_Dnff') and @EventType='CREATE_FUNCTION') set @ReCheck=' 函数的命名必段以[Dnff_]开头' if ((left(@ObjectName,5)<>'Dnfsp' and left(@ObjectName,9)<>'Tmp_Dnfsp') and @EventType='CREATE_PROCEDURE') set @ReCheck=' 存储过程的命名必段以[Dnfsp_]开头' if ((left(@ObjectName,4)<>'idx_' and left(@ObjectName,8)<>'Tmp_idx_') and @EventType='CREATE_INDEX') set @ReCheck=' 索引的命名必段为[idx_+表名+字段名]' if (@ReCheck<>'') begin --DDL触发器测试 /**/ insert Dnft_log_Temp(data01,data02) values(@ObjectName,@EventType) --select * from Dnft_log_Temp --delete Dnft_log_Temp PRINT ''+char(13)+char(10) +'+++++++++++++++++++++++++++++++++++++'+char(13)+char(10) +''+char(13)+char(10) +'请注意: '+char(13)+char(10) +''+char(13)+char(10) +' '+@ReCheck+',如有疑问请查询Dnf开发规范!' +char(13)+char(10) +'----'+char(13)+char(10) +' '+char(13)+char(10) +'---'+char(13)+char(10) +''+char(13)+char(10) +'+++++++++++++++++++++++++++++++++++++'+char(13)+char(10) +''+char(13)+char(10) ROLLBACK; end --DDL更新相关字段数据 select @ObjectName if (left(@ObjectName,4)='Dnft' and @EventType='CREATE_TABLE') begin
exec Dnfsp_sql_OneTableCreateCol @ObjectName,'key_seq','[varchar](50) DEFAULT (newid()) NOT NULL','主键' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'up_uid','[varchar](20) NOT NULL DEFAULT ('''')','更新人账号' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'up_dtime','[datetime] NOT NULL DEFAULT (getdate())','更新日期' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'up_LKey','[varchar](50) NULL','更新键' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'cr_uid','[varchar](20) NOT NULL DEFAULT ('''')','创建人账号' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'cr_dtime','[datetime] NOT NULL DEFAULT (getdate())','创建日期' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'cr_LKey','[varchar](50) NULL','创建键' exec Dnfsp_sql_OneTableCreateCol @ObjectName,'OrgKey','[varchar](50) NULL','组织键' end
GO
SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
DISABLE TRIGGER [tri_DDL_DATABASE_LEVEL_EVENTS] ON DATABASE GO
ENABLE TRIGGER [tri_DDL_DATABASE_LEVEL_EVENTS] ON DATABASE GO
