use tempdb;
go
if exists(select 1 from sysobjects where id=object_id('日志表') and type='U')
drop table 日志表
create table 日志表 (
顺序号 numeric(20) identity ,
操作类型 nvarchar(20) null,
操作对象 varchar(30) null,
操作用户 varchar(30) null default suser_sname(),
操作日期 smalldatetime null default getdate(),
constraint PK_日志表 primary key (顺序号)
)
go
if exists (select 1
from sysobjects
where id = object_id('tCLASS')
and type = 'U')
drop table tCLASS
go
if exists (select 1
from sysobjects
where id = object_id('tSTUDENT')
and type = 'U')
drop table tSTUDENT
go
/*==============================================================*/
/* Table: tCLASS */
/*==============================================================*/
create table tCLASS (
CLASSID numeric not null,
CLASSNAME varchar(20) null,
constraint PK_TCLASS primary key (CLASSID)
)
go
/*==============================================================*/
/* Table: tSTUDENT */
/*==============================================================*/
create table tSTUDENT (
STUDENTID varchar(15) not null,
CLASSID numeric null,
STUDENTNAME varchar(20) null,
Gentle bit null,
Picture image null,
constraint PK_TSTUDENT primary key (STUDENTID)
)
go
alter table tSTUDENT
add constraint FK_TSTUDENT_REFERENCE_TCLASS foreign key (CLASSID)
references tCLASS (CLASSID)
go
insert into tCLASS values(1,'06可视化1班')
insert into tCLASS values(2,'06可视化2班')
insert into tCLASS values(3,'06数据库')
insert into tCLASS values(4,'06WEB1班')
insert into tCLASS values(5,'06WEB2班')
insert into tCLASS values(6,'06移动班')
go
create procedure pUptStudent
@StudentID varchar(30),
@classid integer,
@studentname varchar(30),
@errstr varchar(80) output
as
begin
if @classid is null
begin
select @errstr='班级编号不能为空!'
return -1000
end
if not exists (select classname from tCLASS where CLASSID=@classid)
begin
select @errstr='未找到对应的班级!'
return -1001 /*未找到对应的班级*/
end
begin tran
update tSTUDENT set CLASSID=@classid,STUDENTNAME=@Studentname
where STUDENTID=@StudentID
if @@rowcount=0
begin
rollback tran
select @errstr='未找到相应的学号'
return -1002
end
if @@error <>0
begin
rollback tran
select @errstr='修改失败!'
return -1003
end
insert into 日志表(操作类型,操作对象)
values('修改','tSTUDENT.'+@StudentID)
if @@error <>0
begin
rollback tran
select @errstr='修改失败,插入日志不成功!'
return -1004
end
commit tran
return 0
end
go
go
if exists(select 1 from sysobjects where id=object_id('日志表') and type='U')
drop table 日志表
create table 日志表 (
顺序号 numeric(20) identity ,
操作类型 nvarchar(20) null,
操作对象 varchar(30) null,
操作用户 varchar(30) null default suser_sname(),
操作日期 smalldatetime null default getdate(),
constraint PK_日志表 primary key (顺序号)
)
go
if exists (select 1
from sysobjects
where id = object_id('tCLASS')
and type = 'U')
drop table tCLASS
go
if exists (select 1
from sysobjects
where id = object_id('tSTUDENT')
and type = 'U')
drop table tSTUDENT
go
/*==============================================================*/
/* Table: tCLASS */
/*==============================================================*/
create table tCLASS (
CLASSID numeric not null,
CLASSNAME varchar(20) null,
constraint PK_TCLASS primary key (CLASSID)
)
go
/*==============================================================*/
/* Table: tSTUDENT */
/*==============================================================*/
create table tSTUDENT (
STUDENTID varchar(15) not null,
CLASSID numeric null,
STUDENTNAME varchar(20) null,
Gentle bit null,
Picture image null,
constraint PK_TSTUDENT primary key (STUDENTID)
)
go
alter table tSTUDENT
add constraint FK_TSTUDENT_REFERENCE_TCLASS foreign key (CLASSID)
references tCLASS (CLASSID)
go
insert into tCLASS values(1,'06可视化1班')
insert into tCLASS values(2,'06可视化2班')
insert into tCLASS values(3,'06数据库')
insert into tCLASS values(4,'06WEB1班')
insert into tCLASS values(5,'06WEB2班')
insert into tCLASS values(6,'06移动班')
go
create procedure pUptStudent
@StudentID varchar(30),
@classid integer,
@studentname varchar(30),
@errstr varchar(80) output
as
begin
if @classid is null
begin
select @errstr='班级编号不能为空!'
return -1000
end
if not exists (select classname from tCLASS where CLASSID=@classid)
begin
select @errstr='未找到对应的班级!'
return -1001 /*未找到对应的班级*/
end
begin tran
update tSTUDENT set CLASSID=@classid,STUDENTNAME=@Studentname
where STUDENTID=@StudentID
if @@rowcount=0
begin
rollback tran
select @errstr='未找到相应的学号'
return -1002
end
if @@error <>0
begin
rollback tran
select @errstr='修改失败!'
return -1003
end
insert into 日志表(操作类型,操作对象)
values('修改','tSTUDENT.'+@StudentID)
if @@error <>0
begin
rollback tran
select @errstr='修改失败,插入日志不成功!'
return -1004
end
commit tran
return 0
end
go