实验十

---创建级联
create database shiyan
use shiyan 
go
create database shiyan5
use shiyan5 
go
----创建级联删除与更新
--1）在建表时
create table temp
(
age int
constraint keyf foreign key(age) references student(sage),--第一个级联关系
constraint kef check (age<>0)--第二个级联关系
)
--2)建表后
alter table sc
add constraint sc_student
foreign key(sno) references student(sno) on delete cascade on update cascade


alter table sc
add constraint sc_course 
foreign key(cno) references course(cno)
on delete cascade on update cascade




--删除级联
alter table sc
drop constraint s_c
---




create table S(
SNO nvarchar(4) primary key,
SNAME nvarchar(20) not null,
STATUS int,
CITY nvarchar(30)
)
--------
create table P
(
PNO nvarchar(4) primary key,
PNAME nvarchar(20) not null,
COLOR nvarchar(6),
WEIGHT int default 0
)
-----
create table J(
JNO nvarchar(4) primary key,
JNAME nvarchar(20) not null,
CITY nvarchar(30)
)
drop table J
-----
create table SPJ
(
SNO nvarchar(4) foreign key references S(SNO),
PNO nvarchar(4) foreign key references P(PNO),
JNO nvarchar(4) foreign key references J(JNO),
QTY int check(QTY>1 and QTY<1000)
)


alter table spj
add constraint spj_s
foreign key(sno) references s(sno) 
on delete cascade on update cascade


alter table spj
add constraint spj_p
foreign key(pno) references p(pno)
on delete cascade on update cascade 


alter table spj
add constraint spj_j
foreign key(jno) references j(jno)
on delete cascade on update cascade 


select * from spj




---实验十
--（1）


create trigger s_insert 
on s
for insert 
as if(
select count(*)
from inserted
where status like '%1' or status like '%2'
or status like '%3' or status like '%4' 
or status like '%5'
)=0
begin
print'status字段值不是以1、2、3、4、或5字符打头的！';
rollback transaction
end
drop trigger s_insert
insert into s
values('s10','hello',939,'hannan')


--(2)
--(3)


create trigger j_delete
on j
for delete
as 
declare @i int
select @i =(
select count(*)
from deleted v
where exists
(
select * 
from spj
where spj.qty>=50 and v.jno=spj.jno
)
)
declare @st nvarchar(5)
select @st=(select  jno from deleted)
if(@i>0)
begin 
print'不满足删除条件';
print @i;
print @st;
end
ELSE 
begin 
PRINT @i;
print @st;
end;
rollback transaction;




drop trigger j_delete
delete from j
where jno='j4'
select * from j,spj where j.jno=spj.jno
select * from spj
select * from spj


select * from s where status like '%5'