create database chap2;
use chap2;
create table qcjbxxb (
cph varchar(50) primary key,
qczl varchar(50),
zdzkl varchar(50),
bc varchar(50),
s_name varchar (50)
);
create table cpxxb (
cpbh varchar(50) primary key,
czcs int,
pj int,
bch varchar(50),
yszw int,
yp int,
cfsj varchar(50),
cfd varchar(50),
mdd varchar(50),
glxm varchar(50)
);
create table bcxxb (
bch varchar(50) primary key,
cfd varchar(50),
mdd varchar(50),
cfsj varchar(50),
dzsj varchar(50),
czcs int,
glyxm varchar(50)
);
create table ckxxb (
ckxm varchar(50)primary key,
cpbh varchar(50),
xb varchar(50),
csrq varchar(50)
);
create table czb (
bch varchar(50)primary key,
cfd varchar(50),
mdd varchar(50),
cfsj varchar(50),
dzsj varchar(50),
cph varchar(50),
ckxm varchar(50)
);
create table sjxxb (
s_name varchar(50)primary key,
xb varchar(50),
csrq varchar(50),
jszh varchar(50),
sj varchar(50)
);
create table kyzgly (
glyxm varchar(50),
mm nchar(10)
);
create table xtgly (
dlm nchar(10),
mm nchar(10)
);
create view view_
as select dbo.bcxxb.mdd,dbobcxxb.bch,dbo.bcxxb.cfd,dbo.bcxxb.glyxm,dbo.qcxxb.cph,dbo.qcxxb.qczl,dbo.sjxxb.s_name
from dbo.bcxxb
inner join dbo.qcxxb
on dbo.bcxxb.bch=dbo.qcjbxxb.bch
inner join dbo.sjxxb
on dbo.qcxxb.s_name=dbo.sjxxb.s_name;
SET ansi_nulls on 
go
set quoted_identifier on
go
alter trigger[dbo].[c]
on[dbo].[ckxxb]for insert
as
if exists(select*from dbo.ckxxb,dbo.cpxxb where dbo.cpxxb.cpbh=dbo.ckxxb.cpbh)
begin
update dbo.cpxxb
set yszw=yszw+1,yp=yp-1
where cpbh=(select cpbh from inserted)
end
crytckxx
insert into dbo.ckxxb
values('陆的','00006','男','1993');
set ansi_nulls on 
go
set quoted_identifier on
go
alter trigger [dbo].[b]
on [dbo].[czb] for insert
as
if exists(select*from dbo.czb,dbo.bcxxb where dbo.bcxxb.bxh=dbo.czb.bch)
begin
update dbo.bxxxb
set czcs=czcs+1
where bch=(select bch from inserted)
end
crytczxx
insert into dbo.czb
values('0006','洛阳','西安','14;00','19;00','011','陆平');