Audit functionality using database trigger

USE [dbname]
GO

/****** Object:  DdlTrigger [DDL_Structure_Audit]    Script Date: 03/21/2012 17:57:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE trigger [DDL_Structure_Audit]
on database
for alter_table, drop_table, create_table,alter_procedure,create_procedure,drop_procedure
as

--set nocount on
-- RAISERROR (N'You have not permission to perform this action.', -- Message text.
-- 15, -- Severity,
-- 1 -- State,
-- )
-- Rollback transaction


declare @data xml
declare @vcapture varchar(max)
declare @vUserName varchar(25)
declare @vEventType varchar(50)
declare @vDatabaseName varchar(50)
Declare @ip varchar(75)

set @data = EVENTDATA()

Select @vcapture = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') ,
@vUserName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') ,
@vEventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') ,
@vDatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(50)')


select @ip=
  IpMachineandUserInfo from(
Select top 1 client_net_address+' '+[host_name] as IpMachineandUserInfo from
(
SELECT ec.client_net_address, es.[program_name],
es.[host_name], es.login_name,
ec.session_id
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections  AS ec
ON es.session_id = ec.session_id

)a
where a.login_name=@vUserName
)b

set @vUserName=@vUserName+@ip


if PATINDEX('%PROCEDURE%', upper(@vcapture)) > 0 and upper(@vUserName) = 'shamas'
Begin
Begin Transaction
IF PATINDEX('%DROP%', upper(@vcapture)) > 0
Begin
RAISERROR (N'You have not permission to perform this action.', -- Message text.
15, -- Severity,
1 -- State,
)
Rollback transaction
END
ELSE
BEGIN
INSERT INTO EventLogForDB.dbo.EvtLog  (PostTime ,LoginName , EventType ,TSQLCommand,DatabaseName )
values (getdate(),@vUserName,@vEventType,@vcapture,@vDatabaseName)
Commit Transaction
END
END
ELSE
INSERT INTO EventLogForDB.dbo.EvtLog  (PostTime ,LoginName , EventType ,TSQLCommand,DatabaseName )
values (getdate(),@vUserName,@vEventType,@vcapture,@vDatabaseName)