在A表中存储了图书的编号(book_id)和库存量等信息。在B表中存储了图书的编号(book_id),价格,作者等信息。在删除A表中某图书的信息时,应该同时删除B表中相关的数据行。例如:
A:book_id s
1 123
2 127
3 465
....
B: book_id price
1 23.5
2 89
3 56
4 29
要删除A表中的(3,465)这条记录时,将B表的(3,56)也要删掉。编写触发器如下:
CREATE TRIGGER delete_book
on A
FOR DELETE
AS
DECLERA @book_id char(5)
SELECT @book_id=book_id
FROM A S,deleted d
WHERE S.book_id=d.book_id
delete
from B
WHERE book_id=@book_id
执行时,A表删除记录后,B表没有变化。为什么?各位朋友顺路过来看看。谢谢啦。
A:book_id s
1 123
2 127
3 465
....
B: book_id price
1 23.5
2 89
3 56
4 29
要删除A表中的(3,465)这条记录时,将B表的(3,56)也要删掉。编写触发器如下:
CREATE TRIGGER delete_book
on A
FOR DELETE
AS
DECLERA @book_id char(5)
SELECT @book_id=book_id
FROM A S,deleted d
WHERE S.book_id=d.book_id
delete
from B
WHERE book_id=@book_id
执行时,A表删除记录后,B表没有变化。为什么?各位朋友顺路过来看看。谢谢啦。
on A
FOR DELETE
AS
delete B from B Inner Join Deleted On B.book_id=Deleted.book_id
GO
on A
for delete
as
begin
delete
from B
-- where (select count(*) from books ,deleted d where books.book_id=d.book_id)>=1
where book_id=(select B.book_id from B ,deleted d where B.book_id=d.book_id)
end
A表删除一条,B表全部删除。为什么?
as
begin
delete
from B
where book_id=(select B.book_id from B ,deleted d where B.book_id=d.book_id)
end
你的代码刚刚时过,还是a表删除一条记录,b表记录全删了。
on delete cascade
Create Table A
(book_id Int,
s Int)Create Table B
(book_id Int,
price Float)
--插入数据
Insert A Values(1, 123)
Insert A Values(2, 127)
Insert A Values(3, 465)Insert B Values(1, 23.5)
Insert B Values(2, 89)
Insert B Values(3, 56)
Insert B Values(4, 29)
GO
--建立触发器
CREATE TRIGGER delete_book ON A
FOR DELETE
AS
delete B from B Inner Join Deleted On B.book_id=Deleted.book_id
GO
--测试
Select * from B
Delete from A Where book_id=3
Select * from B
--删除测试环境
Drop Table A,B
--结果
/*
--删除A表数据前
book_id price
1 23.5
2 89.0
3 56.0
4 29.0--删除A表数据后
book_id price
1 23.5
2 89.0
4 29.0
*/
on delete cascade
能說詳細些嗎?謝謝了
因为我在a表建立了好几个触发器,其中有一个是删a表一条,删b表全部。所以影响你给出的代码运行结果。
你的代码可行。谢谢!
for delete
as
delete B where book_id in (select book_id from deleted)
这种方法在SQL SERVER2000里面创建触发器时不会报错,能正确执行,但是功能不能实现,即当要删除表A的某记录时仍然无法实现级联删除,也就是删除不成功