USE [HRIS]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE TRIGGER [dbAudit_DDLEvents]
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_SCHEMA, 
        RENAME, 
        CREATE_DEFAULT, DROP_DEFAULT,
        CREATE_TABLE, ALTER_TABLE,DROP_TABLE,
        CREATE_VIEW, ALTER_VIEW, DROP_VIEW, 
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
        CREATE_INDEX, CREATE_SPATIAL_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_MASTER_KEY, ALTER_MASTER_KEY, DROP_MASTER_KEY,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
 
    DECLARE 
        @ip VARCHAR(32) =
        (
            SELECT client_net_address
                FROM sys.dm_exec_connections
                WHERE session_id = @@SPID
        );
 
    INSERT dbAudit.dbo.DDLEvents
    (
        EventDate,
	   EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        GETDATE(),
        LEFT(@EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),64), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END





GO

ENABLE TRIGGER [dbAudit_DDLEvents] ON DATABASE
GO