数据库实验

create trigger P_Ins on Part after update,insert as
declare @m_weight int
select @m_weight=WEIGHT from inserted
if @m_weight<=0
begin
    print 'the weight<=0! rollback'
    rollback transaction
end

insert into Part
values('P11','螺丝','红',0)

create trigger J_Update on Project after update as
if UPDATE(CITY) AND UPDATE(JNO)
begin
    print 'cannot update JNO and CITY at the same time!'
    rollback transaction
end

update Project
set JNO='J11', CITY='宁夏'
where JNAME='飞机场'

create trigger t_dele_Supp on Supp after delete as
delete from SPJ
where SNO in
    (select SNO from deleted)

delete
from Supp
where SNO='S1'


select JNO,JNAME,PNO,PNAME,SUM(QTY) qty
into P_P_QTY
from (select SPJ.JNO, JNAME,SPJ.PNO,PNAME,QTY from SPJ JOIN Project on SPJ.JNO=Project.JNO JOIN Part ON SPJ.PNO=Part.PNO) as a
group by JNO,JNAME,PNO,PNAME



create trigger t_p_p_qty on SPJ after insert as
declare @m_pno char(5)
declare @m_qty int
select @m_pno=PNO from inserted
select @m_qty=QTY from inserted
if @m_pno not in (select PNO from P_P_QTY)
begin
    print 'insert value PNO is not exists in P_P_QTY'
    rollback transaction
end
if @m_pno in (select PNO from P_P_QTY)
begin
    update P_P_QTY
    set P_P_QTY.qty=P_P_QTY.qty+@m_qty
    where @m_pno=P_P_QTY.PNO
end

insert into SPJ
values('S1','P7','J4',20)


create proc P_S1
as
begin
    select *
    from Supp
    where CITY='北京'
end

exec P_S1

create proc P_S2
@m_jnoo char(5)
as
begin
    select SNAME,PNAME,JNAME
    from Supp,Part,Project,SPJ
    where SPJ.SNO=Supp.SNO and SPJ.JNO=Project.JNO and SPJ.PNO=Part.PNO and SPJ.JNO=@m_jnoo
end

exec P_S2 'J1'

create proc p_s_info
@m_snoo char(6)
as
begin
    select SPJ.JNO,SPJ.PNO,QTY
    from Supp,Part,Project,SPJ
    where SPJ.SNO=Supp.SNO and SPJ.JNO=Project.JNO and SPJ.PNO=Part.PNO and SPJ.JNO=@m_snoo
end

exec p_s_info 'J2'