Question

级联删除的SQL触发器如何写

现有表
city_info(city_id,title,province_id); 城市表
province_info(province_id,title); 省份表
现在要写一触发器，当删除省份表province_info的一个省份时，同时自动删除属于该省份的城市的信息
类似于 要删除ID为1的省份信息，那么执行。。
delete from province_info where province_id = 1;
delete from city_info where province_id = 1;

Answer 1

--sql代码：
--创建数据库实体：test2010
use master
go

if exists (select * from sysdatabases where name = 'test2010')
drop database test2010
go

create database test2010
go

use test2010
go

create table province(
id int identity(1, 1) not null,
title varchar(50) not null,
primary key(id)
)
go

create table city_info(
id int identity(1, 1) not null,
title varchar(50) not null,
p_id int not null,
primary key(id)
)
go

--触发器：tri_delete

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER tri_delete
ON province
for delete
AS
BEGIN
SET NOCOUNT ON;

declare @prov_id int, @prov_name varchar(50)

if exists (select count(*) from deleted)
select @prov_id = id, @prov_name = title from deleted
delete from city_info where p_id = @prov_id
END
GO
--向数据库插入数据

insert into province values('浙江')
insert into province values('江苏')
insert into province values('江西')

insert into city_info values('宁波', 1)
insert into city_info values('杭州', 1)
insert into city_info values('温州', 1)
insert into city_info values('南京', 2)
insert into city_info values('苏州', 2)
insert into city_info values('扬州', 2)
insert into city_info values('南昌', 3)
--查看两张表中的数据：
select * from city_info
select * from province
--删除数据：
delete from province where title = '江西'
以上我是在SQL Server 2005中检验过的，其实我并不提倡用触发器。其实级联删除就能解决问题了。

可以通过在创建数据库的代码中，增加级联删除的就好了。如下：
--sql代码：
use master
go

if exists(select * from sysdatabases where name = 'test2010')
drop database test2010
go

create database test2010
go

use test2010
go

create table province(
id int identity(1, 1) not null,
title varchar(50) not null,
primary key(id)
)
go

create table city_info(
id int identity(1, 1) not null,
title varchar(50) not null,
p_id int not null,
primary key(id)
)
go

alter table city_info add constraint fk_province foreign key(p_id) references province(id)
on delete cascade
go
--出入测试数据：
insert into province values('浙江')
insert into province values('江苏')
insert into province values('江西')

insert into city_info values('宁波', 1)
insert into city_info values('杭州', 1)
insert into city_info values('温州', 1)
insert into city_info values('南京', 2)
insert into city_info values('苏州', 2)
insert into city_info values('扬州', 2)
insert into city_info values('南昌', 3)
--查看插入的数据：
select * from province
select * from city_info
--检验测试的数据：
delete from province where title = '江西'
--再次查询数据：
select * from province
select * from city_info

Answer 2

在SQLserver中可以手动设置级联删除，手写代码的话容易出错，当然如果你是为了锻炼的话，就像楼上那样写就好。

Answer 3

create or replace trigger tri_province
before insert or update or delete on province_info
for each row
declare
begin
if deleting then
delete from city_info where province_id = :old.province_id;
end if;
end tri_province