Giải bài tập SQL sever - phần Trigger!

Câu 1:

Mã:

select lop.malop, tenlop, count(masv) sosv into SoluongSV
from lop join sinhvien on lop.malop=sinhvien.malop
group by lop.malop, tenlop

Câu 2:

Mã:

create trigger insert_sv on sinhvien
after insert
as
	declare @malop char(10)
	select @malop =malop
from inserted
	update soluongsv
set sosv=sosv+1
where malop=@malop

Câu 3:

Mã:

create trigger update_sv on sinhvien
after insert, delete, update
as
begin
declare @malop char(10)
if not exists (select * from deleted)
begin
	select @malop=malop from inserted
	update soluongsv
	set sosv=sosv+1 where @malop=malop
end
else if not exists (select * from inserted)
begin
	select @malop=malop from deleted
	update soluongsv
	set sosv=sosv-1 where @malop=malop
end
else 
begin
	declare @malopcu char(10)
	declare @malopmoi char(10)
	select @malopcu=malop from deleted
	select @malopmoi=malop from inserted
	if(@malopcu <> @malopmoi)
	begin
		update soluongsv
		set sosv = sosv+1 where @malopmoi = malop
		update soluongsv
		set sosv = sosv-1 where @malopcu = malop
	end
end
end

Câu 4:

Mã:

alter trigger sinhvien_ins
on sinhvien
for  insert,update
as
begin
	declare @tuoi int
	declare @malop nvarchar(10) 
	select @tuoi=year(getdate())-year(ngaysinh)
	from inserted
	if @tuoi< 17
		ROLLBACK TRANSACTION
end

Câu 5:

Mã:

alter trigger trig_5
on sinhvien
for  insert, update
as
begin
	declare @tuoi int
	declare @gioitinh bit
	declare @malop nvarchar(10) 
select @malop=malop from inserted
select @gioitinh = gioitinh from inserted
select @tuoi=year(getdate())-year(ngaysinh) from inserted 
if (@tuoi>45 and @gioitinh=0) or (@tuoi>50 and @gioitinh=1)
	ROLLBACK TRANSACTION
end

Câu 6:

Mã:

create trigger rb_svt on sinhvien
for insert, update
as
begin
declare @sosv int
declare @lop nvarchar(10)
select @lop=malop from inserted	
	select @sosv= count(masv) 
				  from sinhvien where @lop= malop
	if @sosv>3
		ROLLBACK TRANSACTION
end

Câu 7:

Mã:

create trigger Cam
on database
for DROP_VIEW, ALTER_VIEW
as
PRINT ' You must disable trigger"Cam" to drop tr alter view'
	ROLLBACK

Các bạn tham khảo sữa chữa thêm cho boy với... Hì hì mới học hôm qua về vọc xí

thank

thank you vr m...

Câu 2 làm gọn tí nek:

Mã:

create trigger tg_cau2
on sinhvien
after insert
as
begin
	update soluongsv
	set soluong=soluong+1
	where malop=(select malop from inserted)
end

Câu 3:

Mã:

create trigger tg_cau3
on sinhvien
after insert,delete,update
as
begin
	update soluongsv
	set soluong=(select count(sinhvien.masv) from sinhvien inner join lop on sinhvien.malop=lop.malop
	where soluongsv.malop=sinhvien.malop)
end

Cấu 4: dư cái này

Mã:

declare @malop nvarchar(10)