zcbus_ddl_dblog_trigger

USE [lzw]
GO

/** Object: DdlTrigger [zcbus_ddl_dblog_trigger] Script Date: 12/22/2021 06:07:07 **/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [zcbus_ddl_dblog_trigger]
ON DATABASE
FOR DDL_TABLE_EVENTS,DDL_INDEX_EVENTS,RENAME,DDL_EXTENDED_PROPERTY_EVENTS
AS
BEGIN
SET NOCOUNT ON;

declare @EventData xml
set @EventData=EVENTDATA();

if exists(select 1 from sys.tables where name = 'ddl_event_tbl')
begin
 insert into [zcbus].[ddl_event_tbl]([op_time],[event_type],[schema_name],
   [object_name],[object_type],[sql_text]) values
   (
   @EventData.value('(/EVENT_INSTANCE/PostTime)[1]','SMALLDATETIME'),
   @EventData.value('(/EVENT_INSTANCE/EventType)[1]','varchar(64)'),
   @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(128)'),
   @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(128)'),
   @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(64)'),
   @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
   );
end  

END;

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [zcbus_ddl_dblog_trigger] ON DATABASE
GO

ENABLE TRIGGER [zcbus_ddl_dblog_trigger] ON DATABASE
GO