Create trigger CheckTrigger
On database
For create_procedure, alter_procedure
.
.
.

Create trigger SCHEMA_AUDIT on  database 
	for 	CREATE_PROCEDURE,  ALTER_PROCEDURE,  DROP_PROCEDURE,
			CREATE_INDEX , ALTER_INDEX  ,DROP_INDEX,
			CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
			ALTER_TABLE
  
as
begin
set nocount on

			/*
			Ref			: 
			Name			: DDL_TRIGGERS.sql
			Version			: 1.0
			Author			: Glen Wass
			Date Created	:22/05/2012
			Description		: create audit table and audit trigger to track stored procedure trigger index creation and changes
			Impact			: 
			Warnings		: 
			Version History	: 			Date			Version		Author		Comments
							---------------------------------------------------------------------------
								22/05/2012	|			1.0			| GW	 	| Initial release
								06/08/2012	|			1.1			| GW		| addded alter table
						
create table VEH_SCHEMA_LOG (id int identity,
													USER_NAME nvarchar(255),
													EVT_TYPE nvarchar (255),
													OBJ_NAME nvarchar(255),
													OBJ_TYPE nvarchar(255),
													CREATE_DTTM datetime)
																							
																							
	--- to stop users from altering the log table																						
														DENY DELETE, UPDATE, ALTER on VEH_SCHEMA_LOG to [domain\DeptSqlUsers],




						
			*/

			Declare @data xml
			set @data=Eventdata()

			Insert into VEH_SCHEMA_LOG values
			(
			@data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)'),
			@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)'),
			@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(255)'),
			@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(255)'),
			getdate()
			

			)
		set nocount off
			
end