Untitled
unknown
plain_text
2 months ago
1.8 kB
3
Indexable
use master go create database QLsv1 go use QLsv1 create table sv( masv char(10) not null primary key, hoten nvarchar(20), que nvarchar(20), malop char(10) not null ) create table lop( malop char(10) not null primary key, tenlop nvarchar(20), siso int ) alter table sv add constraint fk_sv_lop foreign key(malop) references lop(malop) insert into lop values('L01', N'CNTT1', 80) insert into lop values('L02', N'CNTT2', 45) insert into lop values('L03', N'CNTT3', 78) insert into sv values('sv01', N'Nguyen Van A', N'Ha Noi', 'L01') insert into sv values('sv02', N'Nguyen Van B', N'Ha Noi', 'L02') insert into sv values('sv03', N'Nguyen Van C', N'Ha Noi', 'L03') insert into sv values('sv04', N'Nguyen Van D', N'Ha Nam', 'L01') insert into sv values('sv05', N'Nguyen Van E', N'Ha Nam', 'L02') select * from sv select * from lop --viet trigger insert sinh vien , moi khi insert sinh vien -- hay kiem tra xem malop co ton tai hay khong -- siso co vuot qua 70 khong create trigger trg_insertsv on lop for insert as begin declare @ml char(10) set @ml = (select malop from inserted) if(not exists(select * from lop where malop = @ml)) begin raiserror(N'Khong ton tai ma lop nay', 16, 1) rollback transaction end else begin declare @sisos int set @sisos = (select siso from inserted) if(@sisos <70) begin raiserror(N'Khong thoa man', 16, 1) rollback transaction end else update lop set siso = siso +1 where malop = @ml end end insert into lop values('L04', N'CNTT1', 70) select * from lop --viet trigger update bang sinh vien thay doi malop cho sinh vien -- kiem tra xem lop moi co >70 khong? -- cap nhap lai si so lop cu -1, si so lop moi +1
Editor is loading...
Leave a Comment