求一个删除记录的触发器

[cxfeb]

数据库是sql2000
下面是创建的两个表
CREATE TABLE Depts
(
DeptNo CHAR(4) PRIMARY KEY,
DeptName CHAR(20),
);

CREATE TABLE Students
(
Sno CHAR(10) PRIMARY KEY,
Sname CHAR(16),
DeptNo CHAR(4),
StartDate DATETIME,
CreditHours INT,
CONSTRAINT Student_dept FOREIGN KEY(DeptNo) REFERENCES Depts,
);

我想要创建一个触发器，使我删除Depts的记录时，同时删除Students中的相应DeptNo的记录
我写了一个
CREATE TRIGGER del_dept_stu ON dbo.Depts
FOR DELETE
AS
delete from Students where Students.DeptNo in (select DeptNo from deleted)
不过没起作用
请问，到底应该怎么写啊

[cxfeb]

还是删出失败拉

[handong890]

建议主键 外键约束

[handong890]

TABLE Depts 设主键ID 和 TABLE Students外键ID关联 就可以实现了

[javabeginner2006]

建议不要用触发器.明明可以手写代码删除的嘛.

[li_d_s]

CREATE TRIGGER del_dept_stu ON dbo.Depts
FOR DELETE
AS
declare @deptno char(4)
begin
select @deptno = DeptNo from deleted
delete from Students where DeptNo = @deptno
end
go

[lb218]

CREATE TRIGGER del_dept_stu ON dbo.Depts
FOR DELETE
AS
delete from Students where Students.DeptNo =deleted.DeptNo

[pfa2008]

CREATE TRIGGER del_dept_stu ON dbo.Depts
FOR DELETE
AS
declare @DpNo char(4)
select @DpNo=DeptNo from deleted

delete from Students
where DeptNo=@DpNo

[cxfeb]

都不行啊，究竟哪里不对啊