sql在线用户数据库升级.sql

其主要的功能就是,进行后台管理模块整合. 1)其可以支持多个应用,多个模块的权限管理. 2)支持Access和MsSql200/2005. 3)采用角色来进行权限的授权,每个用户可以属于多个角色

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_OnlineInsertUpdateDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sys_OnlineInsertUpdateDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_Online]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sys_Online]
GO

CREATE TABLE [dbo].[sys_Online] (
	[OnlineID] [int] IDENTITY (1, 1) NOT NULL ,
	[O_SessionID] [varchar] (24) NOT NULL ,
	[O_UserName] [nvarchar] (20) NOT NULL ,
	[O_Ip] [varchar] (15) NOT NULL ,
	[O_LoginTime] [datetime] NOT NULL ,
	[O_LastTime] [datetime] NOT NULL ,
	[O_LastUrl] [nvarchar] (500) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[sys_Online] ADD 
	CONSTRAINT [PK_SYS_ONLINE] PRIMARY KEY  CLUSTERED 
	(
		[O_SessionID]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- 创建更新删除
CREATE PROCEDURE sys_OnlineInsertUpdateDelete
(	

	@OnlineID	 int = 0,	-- 自动ID
	@O_SessionID	 varchar(24) = '',	-- 用户SessionID
	@O_UserName	 nvarchar(20) = '',	-- 用户名
	@O_Ip	 varchar(15) = '',	-- 用户IP地址
	@O_LoginTime	 datetime = Null,	-- 登陆时间
	@O_LastTime	 datetime = Null,	-- 最后访问时间
	@O_LastUrl	 nvarchar(500) = '',	-- 最后请求网站
	@DB_Option_Action_  nvarchar(20) = ''		-- 操作方法 Insert:增加 Update:修改 Delete:删除 
)
AS
	DECLARE @ReturnValue int -- 返回操作结果
	
	SET @ReturnValue = -1
	
	-- 新增
	IF (@DB_Option_Action_='Insert')
	BEGIN
		INSERT INTO sys_Online( 
			O_SessionID,
			O_UserName,
			O_Ip,
			O_LoginTime,
			O_LastTime,
			O_LastUrl
		) VALUES (	
			@O_SessionID,
			@O_UserName,
			@O_Ip,
			@O_LoginTime,
			@O_LastTime,
			@O_LastUrl
		)
		SET @ReturnValue = @@IDENTITY
	END
	
	-- 更新
	IF (@DB_Option_Action_='Update')
	BEGIN
		UPDATE sys_Online SET	
			O_SessionID = @O_SessionID,
			O_UserName = @O_UserName,
			O_Ip = @O_Ip,
			O_LoginTime = @O_LoginTime,
			O_LastTime = @O_LastTime,
			O_LastUrl = @O_LastUrl
		WHERE (OnlineID = @OnlineID)
		
		SET @ReturnValue = @@ROWCOUNT
	END
	
	-- 删除
	IF (@DB_Option_Action_='Delete')
	BEGIN
		DELETE sys_Online	WHERE (OnlineID = @OnlineID)
		SET @ReturnValue = @@ROWCOUNT
  	END

SELECT @ReturnValue



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO