Giúp đỡ 2 câu thêm SSDO

[manhchjm]

Mình còn 2 câu không biết làm thế nào về trigger :X cảm ơn các bạn gjup đỡ mình 2 câu trước


--Bang Student
Create table Student
(
RN int not null,
Name nvarchar(50),
Age int,
Gender varchar(20)
)
go
--Bang Subject
Create table Subject
(
sID int not null,
sName nvarchar(50)
)
go
--Bang StudentSubject
Create table StudentSubject
(
RN int not null,
sID int not null,
Mark int ,
Date datetime

)
go


-----1.a tao khoa chinh-----
Alter table Student add constraint PK_Student primary key (RN)
Alter table Subject add constraint PK_Subject primary key (sID)
Alter table StudentSubject add constraint PK_StudentSubject primary key(RN,sID)
go


---1.b Mark trong 0 toi 10-----
Alter table StudentSubject add constraint C_Mark check(Mark between 1 and 10)
go


---1.c Dat rang buoc giua 2 bang Student va StudentSubject---
Alter table StudentSubject add constraint FK_StudentSubject foreign key (RN) references Student(RN)
go

------2 nhap du lieu vao bang-----
--- Cau 2 ---
insert into Student values('1','My Linh',null,null)
insert into Student values('2','Dam Vinh Hung',null,null)
insert into Student values('3','Kim Tu Long',null,null)
insert into Student values('4','Tai Linh',null,null)
insert into Student values('5','My Le',null,null)
insert into Student values('6','Ngoc Oanh',null,null)
go
insert into Subject values('1','SQL')
insert into Subject values('2','LGC')
insert into Subject values('3','HTML')
insert into Subject values('4','CF')
go
insert into StudentSubject values('1','1','8','7/28/2005')
insert into StudentSubject values('2','2','3','7/29/2005')
insert into StudentSubject values('3','3','9','7/31/2005')
insert into StudentSubject values('4','1','5','7/30/2005')
insert into StudentSubject values('5','4','10','7/19/2005')
insert into StudentSubject values('6','1','9','7/25/2005')

go
---Check du lieu---
Select * from Student
Select * from Subject
Select * from StudentSubject

---3a.Cap nhap gioi tinh--
update Student set Gender=0
where Name='My Linh' or Name='Tai Linh' or Name='My Le'
go
--3b.cap nhap gioi tinh---
update Student set Gender=1
where Name='Kim Tu Long'
go
--4.Nhap cac mon hoc vao bang Subject---
insert into Subject values(5,' Core Java')
insert into Subject values(6,' VB.Net')
go

---------------14. Them bang top3----------
--Tao bang top 3
Select Top 3 Mark,Name,sName,Date
into Top3
from Student st inner join StudentSubject ss on st.Rn=ss.Rn
inner join Subject su on su.sID=ss.sID order by Mark desc
--them cot Rank
Alter table top3 add Rank int identity(1,1)
--Check
Select * from top3

------------------------------
18.Tạo 1 trigger casetrong bảng Subject. Sau khi sửa dữ liệu trong côt sID thì cột siD trong bảng StudentSubject thay đổi
19.Tạo 1trigger tên tgtop3 trên bảng StudentSubject dảm bảo dữ liệu trong bảng top3 luôn dữ liệu của 3 người cao điêm nhất.

[bongbingo]

câu 18:


create TRIGGER TriggerUpdateStudentSubject
ON Subject
For Update
as
--get OldSid va NewSid
declare @OldSid int
declare @NewSid int
set @OldSid= (select sId from deleted)
set @NewSid= (select sId from inserted)

-- update to StudentSubject
update StudentSubject
set sID = @NewSid
where sID=@OldSid

câu 19 cũng tương tự, bạn suy nghĩ thêm nhé :)

[manhchjm]

create TRIGGER TriggerUpdateStudentSubject
ON Subject
For Update
as
--get OldSid va NewSid
declare @OldSid int
declare @NewSid int
set @OldSid= (select sId from deleted)
set @NewSid= (select sId from inserted)

-- update to StudentSubject
update StudentSubject
set sID = @NewSid
where sID=@OldSid
-- bo rang buoc thi duoc
Alter StudentSubject drop constraint fk_StudentSubject
............................................
Create trigger caster on Student
instead of Update
as
Declare @moi int
declare @cu int
set @moi=(Select RN from inserted)
set @cu=(Select RN from deleted)
update Student
set RN=@moi where Rn=@cu
update StudentSubject
set RN=@moi where Rn=@cu
go
khong biet chuan hon khong nhi

[bongbingo]

Ko hiểu bạn đang làm cho câu mấy? đề bài như thế nào?

còn trigger ở trên do mình test ở database chưa thiết lập quan hệ hì hì. vậy thì phải
--drop constraint trước khi update
Alter StudentSubject drop constraint fk_StudentSubject

--tạo lại constraint khi update xong bên bảng StudentSubject
Alter table StudentSubject add constraint PK_StudentSubject primary key(RN,sID)