比如drop table student
drop table BorrowRecordCreate Table Student( --学生表
StudentID int primary key, --学号
) Create Table BorrowRecord( --学生借书记录表
BorrowRecord int identity(1,1), --流水号
StudentID int --学号
)
alter table BorrowRecord add constraint fk foreign key(StudentID) references student(studentID)我要删掉一个学生,那么他对应的借书记录也要删掉,请问怎么写触发器 不要 ON DELETE CASCADE有高手指点没
drop table BorrowRecordCreate Table Student( --学生表
StudentID int primary key, --学号
) Create Table BorrowRecord( --学生借书记录表
BorrowRecord int identity(1,1), --流水号
StudentID int --学号
)
alter table BorrowRecord add constraint fk foreign key(StudentID) references student(studentID)我要删掉一个学生,那么他对应的借书记录也要删掉,请问怎么写触发器 不要 ON DELETE CASCADE有高手指点没
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
END
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
END
On Student
for Delete
As
Delete BorrowRecord
From BorrowRecord br , Deleted d
Where br.StudentID=d.StudentID 这是我自己写的,但是有外键约束不行啊,用instead of 学生表里学生信息就不会被删掉了
create table pro1(id int identity(1,1) primary key,[pro1name] varchar(10))
create table pro2(id int identity(1,1) primary key,pro2pro1id int,[pro2name] varchar(10))
alter table pro2 add constraint fk_1 foreign key(pro2pro1id) references pro1(id)set nocount on
insert into pro1 values('船')
insert into pro1 values('车')insert into pro2 values(1,'轮船')
insert into pro2 values(1,'游艇')
insert into pro2 values(2,'卡车')
insert into pro2 values(2,'轿车')
gocreate trigger pro1_del on pro1
instead of delete
as
begin
delete t from pro2 t,deleted e where t.pro2pro1id = e.id
delete t from pro1 t,deleted e where t.id = e.id
end
gobegin transaction
delete from pro1 where id = 1
commit transactionselect * from pro1
select * from pro2set nocount off
drop trigger pro1_del
drop table pro2,pro1/************id pro1name
----------- ----------
2 车id pro2pro1id pro2name
----------- ----------- ----------
3 2 卡车
4 2 轿车
这样试试吧!