Question

SQL触发器同步删除数据要怎么写？

两个表，读者表和借书表，我如果想在删除读者表里的某一行数据时，触发器同时删除借书表里对应的数据，要怎么写？





Answer 1

使用delete 触发器格式：
CREATE trigger tri_update
on tablename
for delete
as
begin
--sql code
end

例：
CREATE trigger tri_update
on tablename1 --触发的表名
for delete
as
begin
declare @del_id varchar(40)
select @del_id = id from deleted --tablename1 中删除的数据保存在 deleted 中
delete tablename2 where id = @del_id --同步删除tablename2中数据
end

Answer 2

上面的回答错误的，应该为：

CREATE TRIGGER T1 ON 读者
INSTEAD OF DELETE AS
DELETE 借书 WHERE 读者编号=(SELECT 读者编号 FROM deleted)

Answer 3

CREATE TRIGGER A_DEL
ON [读者] FOR DELETE
AS
--SET NOCOUNT ON
DELETE E
FROM [读者] E
INNER JOIN deleted [借书] ON E.[读者编号] = [借书].[读者编号]