SELECT Info.uid, Department.did, Department.department /*选择员工ID,部门ID,部门名称*/ FROM Department INNER JOIN
Role ON Department.did = Role.depart INNER JOIN
User_Role ON Role.rid = User_Role.rid INNER JOIN Info ON User_Role.uid = Info.uid
/*根据角色ID相等连接角色表和用户角色映射表,根基用户ID相等连接用户表和用户角色映射表,从而实现这三个表的连接*/
/*职工信息表*/
/*创建于表 信息表(Info),部门表(Department),角色表(Role),职位表(Zhiwei),部门表(Department),用户角色映射表表(User_Role )上*/ SELECT Info.uid, Info.name, Info.phone, Sex.sex ,Zhiwei.zhiwei,Department.department,
/*选择员工ID,员工姓名。员工电话,性别,职位名称,部门*/
FROM Sex INNER JOIN
Info ON Sex.id = Info.uid INNER JOIN
User_Role ON Info.uid = User_Role.uid INNER JOIN Department INNER JOIN
Role ON Department.did = Role.depart ON User_Role.rid = Role.rid INNER JOIN
Zhiwei ON Role.zhiwei = Zhiwei.zid
/*根据性别ID相等连接性别表和员工信息表,根据用户ID相等连接员工表和角色用户映射表,根据角色ID相等连接角色表和角色用户映射表,根据部门ID相等连接部门表和角色表,根据职位ID连接角色表和职位表*/
4.3 存储过程、触发器等的创建SQL代码
16 / 29
4.3.1 触发器SQL代码
图4.3.1 触发器图
/*该触发器是用于每插入一条员工信息就在用户表上添加一条用户ID等于插入的员工ID,密码为“1”的记录*/
create trigger Insert_UserTable/*触发器名称为Insert_UserTable*/
on Info after insert as begin
declare @newuid int /*声明参数newuid */ select @newuid = uid from inserted insert into UserTable values(@newuid,1) end
4.3.2 存储过程SQL代码
17 / 29
图4.3.2 存储过程列表图
①/*创建验证登录信息的存储过程Admin_login*/
CREATE procedure [dbo].[Admin_login](@uid int,@pswd nvarchar(50)) as
declare @pid int begin
select rid from Role where rid=(select rid from User_role where uid=(select uid from User where uid=@uid and pswd=@pswd)) end
②/*创建删除行级的存储过程Del_line,每次删除行级信息时调用此存储过程*/
18 / 29
create procedure [dbo].[Del_line](@uid int) as
delete from Info where uid=@uid
③/*创建获取用户对应角色的权限的存储过程Get_permission,每次用户登录并选好角色后获取该用户该角色状态下的所有权限*/ create procedure [dbo].[Get_permission](@rid int) as
select poject from 权限映射视图 where rid=@rid
④/*利用用户信息中的职位和部门,创建在用户登录成功后获得该用户对应的角色名的存储过程get_rid*/
create procedure get_rid(@zhiwei int,@depart int) as
select rid from Role where zhiwei=@zhiwei and depart=@depart
⑤/*利用用户的角色ID创建得到该用户所在部门的存储过程Get_depart*/ create procedure Get_depart(@rid int) as
select did from 角色部门选择视图
⑥/*根据插入员工信息表单的员工ID,部门和职位,创建在用户角色表中插入用户角色元组的存储过程Insert_User_Role*/
create procedure Insert_User_Role(@uid int ,@rid int,,@zhiwei int,@zid int) as
declare @res int
if(@zid>=@zhiwei)/*操作者的职位必须是属于要修改的人员的职位以上的*/ begin if(@rid=0)
19 / 29
begin
delete from Info where uid=@uid set @res=0 end else begin
insert into User_role (uid,rid) values(@uid,@rid) set @res=1 end end else begin
delete from Info where uid=@uid set @res=0 end
return @res
⑦/创建当职工进行密码修改时获取修改的新密码,跟新用户表中的相应用户的密码的存储过程Update_pswd*/
create procedure Update_pswd(@uid int,@pswd nvarchar(50)) as
update UserTable set pswd=@pswd where uid=@uid
⑧/*利用用户的角色ID创建得到该用户所属职位的职位ID的存储过程Get_zid*/ create procedure Get_zid(@rid int) as
select zid from 员工身份判断视图 where rid=@rid
20 / 29