Audit login trigger

USE [AuditDB] GO /****** Object: DdlTrigger [AuditObjects] Script Date: 2/15/2023 12:24:21 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [AuditObjects] ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_VIEW,ALTER_VIEW,DROP_VIEW,DROP_TRIGGER, CREATE_TRIGGER,ALTER_TRIGGER AS BEGIN SET NOCOUNT ON DECLARE @data XML DECLARE @HostName VARCHAR(500) SET @data = EVENTDATA() SET @HostName = HOST_NAME() INSERT INTO AuditDB.dbo.ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand,date) VALUES( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'), @HostName, @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), GETDATE() ) END EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default', @recipients = 'naresh.koudagani@frontpointsecurity.com', @subject = 'Object has been Altered/Created/Dropped! please look at Audtdb.dbo.ObjectTracking' , @body_format = 'HTML', @importance = 'High' GO ENABLE TRIGGER [AuditObjects] ON DATABASE GO