求一个删除记录的触发器
数据库是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)
不过没起作用
请问，到底应该怎么写啊
[解决办法]
建议不要用触发器.明明可以手写代码删除的嘛.
[解决办法]
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
[解决办法]
CREATE TRIGGER del_dept_stu ON dbo.Depts
FOR DELETE
AS
delete from Students where Students.DeptNo =deleted.DeptNo
[解决办法]
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
[解决办法]
建议主键 外键约束
