PMS.rar_java管理系统_java系统_pms停车场系统_停车场_停车场系统

/*车辆类型*/ create table cartype ( id int IDENTITY(1,1) primary key, typename varchar(20), fare float not null default 0 ) /*系统用户角色*/ create table userrole ( id int IDENTITY(1,1) primary key, rolename varchar(20) ) /*停车卡类型*/ create table cardtype ( id int IDENTITY(1,1) primary key, typename varchar(20), discount float not null ) /*位置*/ create table position ( id int identity(1,1) primary key, cartype_id int , direction varchar(20) not null unique, isuse varchar(4), foreign key (cartype_id) references cartype(id) ) /*停车卡*/ create table car ( id int identity(1,1) primary key, number varchar(20) not null unique, cartype_id int, carmaster varchar(20), cardtype_id int, money float not null default 0, isdelete varchar(20), foreign key (cartype_id) references cartype(id), foreign key (cardtype_id) references cardtype(id), ) /*系统用户*/ create table userinfo ( id int identity(1,1) primary key, username varchar(10), userpassword varchar(10), role_id int, foreign key (role_id) references userrole(id) ) /*车辆--车位*/ create table car_position ( id int identity(1,1) primary key, car_id int , position_id int , begintime datetime, endtime datetime, fare float, ishistory varchar(10), foreign key (car_id) references car(id), foreign key (position_id) references position(id) ) /*触发器*/： create trigger parkout on car_position for update as declare @id int, @car_id int , @position_id int, @hour int , @discount float, @fare float, @cardtype_id int, @cartype_id int , @money float set @id = (select id from inserted) set @car_id = (select car_id from inserted) set @position_id = (select position_id from inserted) set @hour = (select DateDiff(HOUR,begintime,endtime) from inserted) begin set @cardtype_id = (select cardtype_id from car where id = @car_id) set @cartype_id = (select cartype_id from car where id = @car_id) set @fare = (select fare from cartype where id = @cartype_id) set @discount = (select discount from cardtype where id = @cardtype_id) /10 set @money = @hour * @discount * @fare update car_position set fare = @money where id = @id update car set money = money - @money where id = @car_id update position set isuse = '否' where id = @position_id update car_position set ishistory = '是' where id = @id end