GO
/****** Object:  Table [dbo].[nextIDValue]    Script Date: 08/01/2008 14:49:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nextIDValue](
	[ParameterName] [varchar](255) NOT NULL,
	[NextIDValue] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ParameterName] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

GO
/****** Object:  Table [dbo].[ErrorApp]    Script Date: 08/01/2008 14:49:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorApp](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_ErrorAppName_IsDeleted]  DEFAULT (0),
	[CreateName] [varchar](50) NOT NULL,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ErrorAppName_CreateDate]  DEFAULT (getdate()),
	[ChangeName] [varchar](50) NULL,
	[ChangeDate] [datetime] NULL,
 CONSTRAINT [PK_ErrorAppName] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ErrorModule]    Script Date: 08/01/2008 14:49:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorModule](
	[ID] [bigint] NOT NULL,
	[App_ID] [bigint] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_ErrorModule_IsDeleted]  DEFAULT (0),
	[CreateName] [varchar](50) NOT NULL,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ErrorAppModule_CreateDate]  DEFAULT (getdate()),
	[ChangeName] [varchar](50) NULL,
	[ChangeDate] [datetime] NULL,
 CONSTRAINT [PK_ErrorAppModule] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ErrorProcedure]    Script Date: 08/01/2008 14:49:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorProcedure](
	[ID] [bigint] NOT NULL,
	[Module_ID] [bigint] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[IsDeleted] [bit] NULL CONSTRAINT [DF_ErrorProcedure_IsDeleted]  DEFAULT (0),
	[CreateName] [varchar](50) NOT NULL,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ErrorProcedure_CreateDate]  DEFAULT (getdate()),
	[ChangeName] [varchar](50) NULL,
	[ChangeDate] [datetime] NULL,
 CONSTRAINT [PK_ErrorProcedure] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ErrorEvent]    Script Date: 08/01/2008 14:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorEvent](
	[ID] [bigint] NOT NULL,
	[Procedure_ID] [bigint] NOT NULL,
	[Exception] [varchar](50) NULL,
	[Source] [varchar](2000) NULL,
	[Description] [varchar](2000) NOT NULL,
	[Host] [varchar](50) NOT NULL,
	[User] [varchar](50) NOT NULL,
	[PostDate] [datetime] NOT NULL CONSTRAINT [DF_ErrorEvent_PostDate]  DEFAULT (getdate()),
	[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_ErrorEvent_IsDeleted]  DEFAULT (0),
	[CreateName] [varchar](50) NOT NULL,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ErrorEvent_CreateDate]  DEFAULT (getdate()),
	[ChangeName] [varchar](50) NULL,
	[ChangeDate] [datetime] NULL,
 CONSTRAINT [PK_ErrorEvent] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[p_GetNextIDValue]    Script Date: 08/01/2008 14:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[p_GetNextIDValue](  			
			@ParameterName varchar(255),  			
			@NextIDValue bigint OUTPUT) AS  
			
set @NextIDValue=-1    
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  	  	
begin transaction  	

select @NextIDValue=(select NextIDValue from NextIDValue where ParameterName=@ParameterName)    
if @@error=0  
begin  	
  	
	if  @NextIDValue is null   	
	begin  		
		set @NextIDValue=1  		
		insert into NextIDValue(NextIDValue,ParameterName) values (@NextIDValue,@ParameterName)  	
	end  	  	
	else  	  	
	begin  		
		set @NextIDValue=@NextIDValue+1  		
		update NextIDValue set NextIDValue=@NextIDValue where ParameterName=@ParameterName  	
	end  	
  	
	if @@error=0  		
		commit transaction  	
	else  	
		begin  		
			rollback transaction  		
			set @NextIDValue=-1  	
		end  
end    
else
	begin  		
		rollback transaction  		
		set @NextIDValue=-1  	
	end  	

print @NextIDValue
GO
/****** Object:  StoredProcedure [dbo].[p_ErrorGetAppIDByName]    Script Date: 08/01/2008 14:49:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[p_ErrorGetAppIDByName](
					@name varchar(255), 
					@OUTPUT_AppID bigint output) 
as

declare @ID bigint

select 
	@ID=a.[ID]
from
	[ErrorApp] a
where
	a.[isdeleted]=0
	and a.[name]=@name

if @ID is null
begin
	exec p_getnextidvalue 'errorapp',@ID output

	insert into ErrorApp(
		[ID],                   
		[Name],                                        
		[CreateName]) values (

		@ID,
		@name,
		'p_ErrorGetAppIDByName')
end

set @OUTPUT_AppID=@ID
GO
/****** Object:  StoredProcedure [dbo].[p_ErrorGetProcedureIDByName]    Script Date: 08/01/2008 14:49:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[p_ErrorGetProcedureIDByName](
					@moduleid bigint,
					@name varchar(255),
					@OUTPUT_procedureid bigint output) 
as

declare @ID bigint

select 
	@ID=a.[ID]
from
	[ErrorProcedure] a
where
	a.[isdeleted]=0
	and a.[module_id]=@moduleid
	and a.[name]=@name

if @ID is null
begin
	exec p_getnextidvalue 'ErrorProcedure',@ID output

	insert into [ErrorProcedure](
		[ID],  
		[Module_ID],                 
		[Name],                                        
		[CreateName]) values (

		@ID,
		@moduleid,
		@name,
		'p_ErrorGetProcedureIDByName')
end

set @OUTPUT_procedureid= @ID
GO
/****** Object:  StoredProcedure [dbo].[p_ErrorGetModuleIDByName]    Script Date: 08/01/2008 14:49:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[p_ErrorGetModuleIDByName](
					@appid bigint,
					@name varchar(255),
					@OUTPUT_moduleid bigint output) 
as

declare @ID bigint

select 
	@ID=a.[ID]
from
	[ErrorModule] a
where
	a.[isdeleted]=0
	and a.[app_id]=@appid
	and a.[name]=@name

if @ID is null
begin
	exec p_getnextidvalue 'errormodule',@ID output

	insert into [ErrorModule](
		[ID],  
		[App_ID],                 
		[Name],                                        
		[CreateName]) values (

		@ID,
		@appid,
		@name,
		'p_ErrorGetModuleIDByName')
end

set @OUTPUT_moduleid= @ID
GO
/****** Object:  StoredProcedure [dbo].[p_ErrorLogEvent]    Script Date: 08/01/2008 14:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[p_ErrorLogEvent](
					@app_name varchar(255),
					@module_name varchar(255),
					@procedure_name varchar(255),
					@exception varchar(50),
					@source varchar(2000),
					@description varchar(2000),
					@host varchar(50),
					@user varchar(50),
					@postdate datetime,		
					@output_eventid bigint output)
as

declare @appid bigint,
		@moduleid bigint,
		@procedureid bigint

exec p_ErrorGetAppIDByName @app_name,@appid output
exec p_ErrorGetModuleIDByName @appid,@module_name,@moduleid output
exec p_ErrorGetProcedureIDByName @moduleid,@procedure_name,@procedureid output

declare @ID bigint
exec p_getnextidvalue 'errorevent',@ID output

insert into dbo.[ErrorEvent] (
	[ID],
	[Procedure_ID],
	[Exception],
	[Source],
	[Description],
	[Host],
	[User],
	[PostDate],
	[CreateName]
) values (
	@ID,
	@procedureid,
	@exception,
	@source,
	@description,
	@host,
	@user,
	@postdate,
	'p_ErrorLogEvent'
)

set @output_eventid=@id
GO
/****** Object:  ForeignKey [FK_ErrorEvent_ErrorProcedure]    Script Date: 08/01/2008 14:49:31 ******/
ALTER TABLE [dbo].[ErrorEvent]  WITH NOCHECK ADD  CONSTRAINT [FK_ErrorEvent_ErrorProcedure] FOREIGN KEY([Procedure_ID])
REFERENCES [dbo].[ErrorProcedure] ([ID])
GO
ALTER TABLE [dbo].[ErrorEvent] CHECK CONSTRAINT [FK_ErrorEvent_ErrorProcedure]
GO
/****** Object:  ForeignKey [FK_ErrorModule_ErrorApp]    Script Date: 08/01/2008 14:49:33 ******/
ALTER TABLE [dbo].[ErrorModule]  WITH NOCHECK ADD  CONSTRAINT [FK_ErrorModule_ErrorApp] FOREIGN KEY([App_ID])
REFERENCES [dbo].[ErrorApp] ([ID])
GO
ALTER TABLE [dbo].[ErrorModule] CHECK CONSTRAINT [FK_ErrorModule_ErrorApp]
GO
/****** Object:  ForeignKey [FK_ErrorProcedure_ErrorModule]    Script Date: 08/01/2008 14:49:36 ******/
ALTER TABLE [dbo].[ErrorProcedure]  WITH NOCHECK ADD  CONSTRAINT [FK_ErrorProcedure_ErrorModule] FOREIGN KEY([Module_ID])
REFERENCES [dbo].[ErrorModule] ([ID])
GO
ALTER TABLE [dbo].[ErrorProcedure] CHECK CONSTRAINT [FK_ErrorProcedure_ErrorModule]
GO