感觉这个最好通过外键约束解决。如果要触发器的话应该是这样, 未测试CREATE TRIGGER TRI_1 ON Students for delete as begin delete a from sc a inner join deleted b on a.stuID = b.stuid end
create trigger trg_delete_student on students for delete as -- If no rows are deleted in table students, do nothing. if @@rowcount = 0 return; -- Delete rows in table sc , whose stuIDs equal to stuIDs of rows in table deleted. delete s from sc s, deleted d where d.stuID=s.stuID; GO
create trigger trg_delete_sc on students for delete as begin delete sc from sc,deleted where sc.stuID=deleted.stuID end
为什么这个触发器是建在students表上,不是在sc表上 delete s from sc s, deleted d where d.stuID=s.stuID; 这句啥意思 deleted是什么表啊??
CREATE TRIGGER TRI_1 ON Students for delete as begin delete a from sc a,deleted b where a.stuID = b.stuid end这个怎么可能不行呢除非2表中,你删除的那条记录和sc 里不存在stuID = sc.stuid
create table students ([sid] int identity, [name] varchar(128)); insert into students([name]) select 'Tom' union all select 'Jerry';create table sc ([sid] int, [score] int, [sname] varchar(128)); insert into sc select 1,70,'English' union all select 2,80,'Math' union all select 1,90,'Math' union all select 2,60,'English';create trigger trg_delete_student on students for delete as -- If no rows are deleted in table students, do nothing. if @@rowcount = 0 return; -- Delete rows in table sc , whose stuIDs equal to stuIDs of rows in table deleted. delete s from sc s, deleted d where d.sid=s.sid; GOdelete from students where [sid]=2select * from students /* 1 Tom */ select * from sc /* 1 70 English 1 90 Math */
看我的IF EXISTS (SELECT name FROM sysobjects WHERE name='student') DROP TABLE student IF EXISTS (SELECT name FROM sysobjects WHERE name='course') DROP TABLE course IF EXISTS (SELECT name FROM sysobjects WHERE name='sc') DROP TABLE scgo/*学号Sno;姓名Sname;性别Ssex;年龄Sage;所在系Sdept*/ create table Student (Sno int, Sname varchar(20)not null, Ssex varchar(10) not null, Sage numeric(3,0)not null, Sdept varchar(20))goinsert into Student values('95001','李勇男','男','20','CS') insert into Student values('95002','万里','男','21','IS') insert into Student values('95003','李琴','女','19','MA') insert into Student values('95004','李三','女','22','CS') insert into Student values('95005','王二麻子','男','20','IS') insert into Student values('95006','李小二','男','19','MA') insert into Student values('95007','吴霞','女','18','IS')go /*课程号Cno;课程名称Cname;学分Cpon;*/ create table Course (Cno int, Cname varchar(10) not null, Cpon int not null)goinsert into Course values('1','数据库','54') insert into Course values('2','英语','60') insert into Course values('3','高等数学','70') insert into Course values('4','C语言','85') insert into Course values('5','操作系统','89') insert into Course values('6','数据结构','96')go /*学号Sno;课程号Cno;成绩Grade*/ create table SC (Sno int, Cno int, Grade int)goinsert into SC values('95001','1','92') insert into SC values('95002','4','66') insert into SC values('95002','2','66') insert into SC values('95002','3','66') insert into SC values('95002','5','66') insert into SC values('95002','4','66') insert into SC values('95002','1','66') insert into SC values('95003','3','59') insert into SC values('95004','2','75') insert into SC values('95005','6','85') insert into SC values('95006','3',null) insert into SC values('95007','4',null) insert into SC values('95008','1','78') insert into SC values('95008','3','65') insert into SC values('95008','4','49') insert into SC values('95008','5','52') insert into SC values('95008','6','80') insert into SC values('95008','2','78') insert into SC values('95009','3','54') insert into SC values('950010','3','67') insert into SC values('950011','1','90') insert into SC values('95001','2','92') insert into SC values('95001','3','50') insert into SC values('95001','4','70') insert into SC values('95001','5','85') insert into SC values('95001','6','64')GO IF EXISTS (SELECT name FROM sysobjects WHERE name='tr_student_delete' AND type='TR')DROP TRIGGER tr_student_deleteGOCREATE TRIGGER tr_student_delete ON student FOR delete AS IF @@ROWCOUNT=0 RETURN ELSE DELETE SC FROM student s INNER JOIN deleted d ON d.sno=s.snoGODELETE FROM student WHERE sno=95001 /*结果显示:
感觉这个最好通过外键约束解决。如果要触发器的话应该是这样, 未测试CREATE TRIGGER TRI_1 ON Students
for delete
as
begin
delete a
from sc a inner join deleted b on a.stuID = b.stuid
end
for delete as
-- If no rows are deleted in table students, do nothing.
if @@rowcount = 0 return;
-- Delete rows in table sc , whose stuIDs equal to stuIDs of rows in table deleted.
delete s from sc s, deleted d where d.stuID=s.stuID;
GO
for delete
as
begin
delete sc
from sc,deleted
where sc.stuID=deleted.stuID
end
为什么这个触发器是建在students表上,不是在sc表上
delete s from sc s, deleted d where d.stuID=s.stuID; 这句啥意思
deleted是什么表啊??
我试了下,没用啊。。我删除了studens表的一条记录,但是sc表的记录并没删除。。
for delete
as
begin
delete a
from sc a,deleted b
where a.stuID = b.stuid
end这个怎么可能不行呢除非2表中,你删除的那条记录和sc 里不存在stuID = sc.stuid
create table students ([sid] int identity, [name] varchar(128));
insert into students([name])
select 'Tom' union all select 'Jerry';create table sc ([sid] int, [score] int, [sname] varchar(128));
insert into sc
select 1,70,'English' union all select 2,80,'Math' union all
select 1,90,'Math' union all select 2,60,'English';create trigger trg_delete_student on students
for delete as
-- If no rows are deleted in table students, do nothing.
if @@rowcount = 0 return;
-- Delete rows in table sc , whose stuIDs equal to stuIDs of rows in table deleted.
delete s from sc s, deleted d where d.sid=s.sid;
GOdelete from students where [sid]=2select * from students
/* 1 Tom */
select * from sc
/*
1 70 English
1 90 Math
*/
IF EXISTS (SELECT name FROM sysobjects WHERE name='course') DROP TABLE course
IF EXISTS (SELECT name FROM sysobjects WHERE name='sc') DROP TABLE scgo/*学号Sno;姓名Sname;性别Ssex;年龄Sage;所在系Sdept*/
create table Student
(Sno int,
Sname varchar(20)not null,
Ssex varchar(10) not null,
Sage numeric(3,0)not null,
Sdept varchar(20))goinsert into Student values('95001','李勇男','男','20','CS')
insert into Student values('95002','万里','男','21','IS')
insert into Student values('95003','李琴','女','19','MA')
insert into Student values('95004','李三','女','22','CS')
insert into Student values('95005','王二麻子','男','20','IS')
insert into Student values('95006','李小二','男','19','MA')
insert into Student values('95007','吴霞','女','18','IS')go
/*课程号Cno;课程名称Cname;学分Cpon;*/
create table Course
(Cno int,
Cname varchar(10) not null,
Cpon int not null)goinsert into Course values('1','数据库','54')
insert into Course values('2','英语','60')
insert into Course values('3','高等数学','70')
insert into Course values('4','C语言','85')
insert into Course values('5','操作系统','89')
insert into Course values('6','数据结构','96')go
/*学号Sno;课程号Cno;成绩Grade*/
create table SC
(Sno int,
Cno int,
Grade int)goinsert into SC values('95001','1','92')
insert into SC values('95002','4','66')
insert into SC values('95002','2','66')
insert into SC values('95002','3','66')
insert into SC values('95002','5','66')
insert into SC values('95002','4','66')
insert into SC values('95002','1','66')
insert into SC values('95003','3','59')
insert into SC values('95004','2','75')
insert into SC values('95005','6','85')
insert into SC values('95006','3',null)
insert into SC values('95007','4',null)
insert into SC values('95008','1','78')
insert into SC values('95008','3','65')
insert into SC values('95008','4','49')
insert into SC values('95008','5','52')
insert into SC values('95008','6','80')
insert into SC values('95008','2','78')
insert into SC values('95009','3','54')
insert into SC values('950010','3','67')
insert into SC values('950011','1','90')
insert into SC values('95001','2','92')
insert into SC values('95001','3','50')
insert into SC values('95001','4','70')
insert into SC values('95001','5','85')
insert into SC values('95001','6','64')GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name='tr_student_delete' AND type='TR')DROP TRIGGER tr_student_deleteGOCREATE TRIGGER tr_student_delete ON student
FOR delete
AS
IF @@ROWCOUNT=0
RETURN
ELSE
DELETE SC FROM student s INNER JOIN deleted d ON d.sno=s.snoGODELETE FROM student WHERE sno=95001
/*结果显示:
(所影响的行数为 0 行)
(所影响的行数为 1 行)
*/
看了下 并没有删除 SC表的数据,请问我代码那个写错了还是别的什么原因?