I am working on creating a module to Track the Visitor to My Site I just Start with the database for this module and I want to see if I'm on the right trake or not
f not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Elnajjar_VisitorsTrackingSystem]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE {databaseOwner}[{objectQualifier}Elnajjar_VisitorsTrackingSystem]
(
[ModuleID] [int] NOT NULL,
[ItemID] [int] NOT NULL IDENTITY(1, 1),
[Content] [ntext] NOT NULL,
[CreatedByUser] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[SessionId] [ntext] NOT NULL,
[UrlPath] [ntext] NOT NULL,
[PageView] [int] NOT NULL
)
ALTER TABLE {databaseOwner}[{objectQualifier}Elnajjar_VisitorsTrackingSystem] ADD CONSTRAINT [PK_{objectQualifier}Elnajjar_VisitorsTrackingSystem] PRIMARY KEY CLUSTERED ([ItemID])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Elnajjar_VisitorsTrackingSystem] ON {databaseOwner}[{objectQualifier}Elnajjar_VisitorsTrackingSystem] ([ModuleID])
ALTER TABLE {databaseOwner}[{objectQualifier}Elnajjar_VisitorsTrackingSystem] WITH NOCHECK ADD CONSTRAINT [FK_{objectQualifier}Elnajjar_VisitorsTrackingSystem_{objectQualifier}Modules] FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
END
GO
/** Drop Existing Stored Procedures **/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Elnajjar_GetVisitorsTrackingSystems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Elnajjar_GetVisitorsTrackingSystems
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Elnajjar_GetVisitorsTrackingSystem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Elnajjar_GetVisitorsTrackingSystem
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Elnajjar_AddVisitorsTrackingSystem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Elnajjar_AddVisitorsTrackingSystem
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Elnajjar_UpdateVisitorsTrackingSystem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Elnajjar_UpdateVisitorsTrackingSystem
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Elnajjar_DeleteVisitorsTrackingSystem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Elnajjar_DeleteVisitorsTrackingSystem
GO
/** Create Stored Procedures **/
create procedure {databaseOwner}{objectQualifier}Elnajjar_GetVisitorsTrackingSystems
@ModuleId int
as
select ModuleId,
ItemId,
Content,
CreatedByUser,
SessionId,
UrlPath,
PageView,
{objectQualifier}Elnajjar_VisitorsTrackingSystem.CreatedDate,
'CreatedByUserName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName
from {objectQualifier}Elnajjar_VisitorsTrackingSystem
inner join {objectQualifier}Users on {objectQualifier}Elnajjar_VisitorsTrackingSystem.CreatedByUser = {objectQualifier}Users.UserId
where ModuleId = @ModuleId
GO
create procedure {databaseOwner}{objectQualifier}Elnajjar_GetVisitorsTrackingSystem
@ModuleId int,
@ItemId int
as
select ModuleId,
ItemId,
Content,
CreatedByUser,
SessionId,
UrlPath,
PageView,
{objectQualifier}Elnajjar_VisitorsTrackingSystem.CreatedDate,
'CreatedByUserName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName
from {objectQualifier}Elnajjar_VisitorsTrackingSystem
inner join {objectQualifier}Users on {objectQualifier}Elnajjar_VisitorsTrackingSystem.CreatedByUser = {objectQualifier}Users.UserId
where ModuleId = @ModuleId
and ItemId = @ItemId
GO
create procedure {databaseOwner}{objectQualifier}Elnajjar_AddVisitorsTrackingSystem
@ModuleId int,
@Content ntext,
@UserID int,
@SessionId ntext,
@UrlPath ntext,
@PageView ntext
as
insert into {objectQualifier}Elnajjar_VisitorsTrackingSystem (
ModuleId,
Content,
CreatedByUser,
CreatedDate,
SessionId,
UrlPath,
PageView
)
values (
@ModuleId,
@Content,
@UserID,
@SessionId,
@UrlPath,
@PageView,
getdate()
)
GO
create procedure {databaseOwner}{objectQualifier}Elnajjar_UpdateVisitorsTrackingSystem
@ModuleId int,
@ItemId int,
@Content ntext,
@UserID int,
@SessionId ntext,
@UrlPath ntext,
@PageView ntext
as
update {objectQualifier}Elnajjar_VisitorsTrackingSystem
set Content = @Content,
CreatedByUser = @UserID,
CreatedDate = getdate()
SessionId = @SessionId,
UrlPath = @UrlPath,
PageView = @PageView
where ModuleId = @ModuleId
and ItemId = @ItemId
GO
create procedure {databaseOwner}{objectQualifier}Elnajjar_DeleteVisitorsTrackingSystem
@ModuleId int,
@ItemId int
as
delete
from {objectQualifier}Elnajjar_VisitorsTrackingSystem
where ModuleId = @ModuleId
and ItemId = @ItemId
GO