本人刚刚学习数据库,对一些东西不是很熟悉,想请教大家。已经创建好下面三个表,
1)如何对课程表Course分别添加以下完整性约束:级联删除、级联修改和拒绝修改,再分别对课程表进行删除数据和修改数据的操作。
2)创建触发器trigger_t2,实现当修改学生表Student中的某个学生的学号时,对应学生选修表SC中的学号也作修改。
3)对已创建的触发器trigger_t1进行修改,实现当修改学生表Student中的数据时,显示提示信息“学生表中XXX号学生记录被修改了” 。并执行触发器--创建 Student表
CREATE TABLE Student /*列级定义主码*/
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(15));
insert into Student values('200215121','李勇','男',20,'CS');
insert into Student values('200215122','刘晨','女',19,'CS');
insert into Student values('200215123','王敏','女',18,'MA');
insert into Student values('200215125','张立','男',19,'IS');
--创建 Course表
CREATE TABLE Course(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
INSERT INTO Course
SELECT '1','数据库','5',4
UNION ALL
SELECT '2','数学',null,2
UNION ALL
SELECT '3','信息系统','1',4
UNION ALL
SELECT '4','操作系统','6',3
UNION ALL
SELECT '5','数据结构','7',4
UNION ALL
SELECT '6','数据处理',null,2
UNION ALL
SELECT '7','PASCAL语言','6',4
--创建 SC表
CREATE TABLE SC(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
INSERT INTO SC
SELECT '200215121','1',92
UNION ALL
SELECT '200215121','2',85
UNION ALL
SELECT '200215121','3',88
UNION ALL
SELECT '200215122','2',90
UNION ALL
SELECT '200215122','3',80
1)如何对课程表Course分别添加以下完整性约束:级联删除、级联修改和拒绝修改,再分别对课程表进行删除数据和修改数据的操作。
2)创建触发器trigger_t2,实现当修改学生表Student中的某个学生的学号时,对应学生选修表SC中的学号也作修改。
3)对已创建的触发器trigger_t1进行修改,实现当修改学生表Student中的数据时,显示提示信息“学生表中XXX号学生记录被修改了” 。并执行触发器--创建 Student表
CREATE TABLE Student /*列级定义主码*/
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(15));
insert into Student values('200215121','李勇','男',20,'CS');
insert into Student values('200215122','刘晨','女',19,'CS');
insert into Student values('200215123','王敏','女',18,'MA');
insert into Student values('200215125','张立','男',19,'IS');
--创建 Course表
CREATE TABLE Course(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
INSERT INTO Course
SELECT '1','数据库','5',4
UNION ALL
SELECT '2','数学',null,2
UNION ALL
SELECT '3','信息系统','1',4
UNION ALL
SELECT '4','操作系统','6',3
UNION ALL
SELECT '5','数据结构','7',4
UNION ALL
SELECT '6','数据处理',null,2
UNION ALL
SELECT '7','PASCAL语言','6',4
--创建 SC表
CREATE TABLE SC(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
INSERT INTO SC
SELECT '200215121','1',92
UNION ALL
SELECT '200215121','2',85
UNION ALL
SELECT '200215121','3',88
UNION ALL
SELECT '200215122','2',90
UNION ALL
SELECT '200215122','3',80
create table zb --主表
(
zb_id int primary key,
)gocreate table cb --从表
(
cb_id int primary key
FOREIGN KEY REFERENCES zb(zb_id) ON DELETE CASCADE , --指定级联删除
)
go--主表数据
insert zb values(1)
insert zb values(2)
insert zb values(3)
--从表数据
insert cb values(1)
insert cb values(2)
insert cb values(3)
select * from zb
select * from cb
zb_id
-----------
1
2
3(所影响的行数为 3 行)mx_id
-----------
1
2
3(所影响的行数为 3 行)
--级联删除
delete from zb where zb_id = 1
select * from zb
select * from cb
zb_id
-----------
2
3(所影响的行数为 2 行)mx_id
-----------
2
3
(所影响的行数为 2 行)alter table cb add constraint fk_cb foreign key(mx_id) references zb(zb_id) on delete cascadealter table 从表 add constraint 外键名 foreign key(从表字段) references 主表(主表字段) on delete cascade看得眼花,给一个例子
on Student
for update
as
begin
if update(Sno)
update ....
end
after update
as
begin
declare @sno char(7) ,@sno1 char(7)
select @sno=sno from deleted
select @sno1=sno from inserted
if update(sno)
print '学生表中'+@sno+'号学生记录被修改了'
update cj
set sno=@sno1
where sno=@sno
endupdate student
set sno='2000100'
where sno='2000101'
select *
from student
select *
from cj
after update
as
begin
declare @sno char(7) ,@sno1 char(7)
select @sno=sno from deleted
select @sno1=sno from inserted
if update(sno)
print '学生表中'+@sno+'号学生记录被修改了'
update cj
set sno=@sno1
where sno=@sno
end
]
after update
as
begin
declare @sno char(7) ,@sno1 char(7)
select @sno=sno from deleted
select @sno1=sno from inserted
if update(sno)
print '学生表中'+@sno+'号学生记录被修改了'
update cj
set sno=@sno1
where sno=@sno
end
]