做的一个简单手机联系人系统经典的三张表。。1。人。 2。组。 3。 关系。。问一下。。可不可以在人表上创建一个删除触发器。。在删除人表中信息的时候。。把组中有该人的信息的资料都删掉我写的一个。。发现不行啊。。
create trigger tri_tab_people_delete on tab_people for delete
as
begin
if exists(select pid from deleted where pid in (select pid from tab_store))
begin
--set identity_insert tab_group on
--set identity_insert deleted on
delete from tab_store where pid in (select pid from deleted)
end
end
create trigger tri_tab_people_delete on tab_people for delete
as
begin
if exists(select pid from deleted where pid in (select pid from tab_store))
begin
--set identity_insert tab_group on
--set identity_insert deleted on
delete from tab_store where pid in (select pid from deleted)
end
end
as
begin
if exists(select pid from tab_store where pid in (select pid from deleted))
begin
--set identity_insert tab_group on
--set identity_insert deleted on
delete from tab_store where pid in (select pid from deleted)
end
end
create table tab_people
(pid int identity(1,1),pname nvarchar(10))
insert tab_people (pname)
select 'kkk1' union all
select 'kkk2' union all
select 'kkk3' union all
select 'kkk4' create table tab_group
(gid int identity(1,1),gname nvarchar(10))
insert tab_group
select 'ggg1' union all
select 'ggg2' union all
select 'ggg3' create table tab_store
(gid int ,pid int)
insert tab_store
select 1,2 union all
select 3,3 union all
select 2,1 union all
select 3,4 create trigger tri_tab_people on tab_people for delete
as
begin
delete tab_store from deleted as d where tab_store.pid=d.pid
end
as
begin
declare @pid varchar(20)
set @pid=(select pid from deleted)
delete from tab_store where pid=@pid
end
1
DELETE 语句与 COLUMN REFERENCE 约束 'FK__tab_store__pid__00551192' 冲突。该冲突发生于数据库 'db_phone_relation',表 'tab_store', column 'pid'。
语句已终止。
要先把从表的数据先删除,这个表的pid在其他表作为外键在使用
create table tab_people
(pid int identity(1,1),pname nvarchar(10))
insert tab_people (pname)
select 'kkk1' union all
select 'kkk2' union all
select 'kkk3' union all
select 'kkk4'
select * from tab_people
create table tab_group
(gid int identity(1,1),gname nvarchar(10))
insert tab_group
select 'ggg1' union all
select 'ggg2' union all
select 'ggg3' create table tab_store
(gid int ,pid int)
insert tab_store
select 1,5 union all
select 3,6 union all
select 2,7 union all
select 3,8union all
select 2,5 union all
select 3,7if exists(select * from sysobjects where name='tri_tab_people' and xtype='TR')
begin
drop trigger tri_tab_people
endcreate trigger tri_tab_people on tab_people instead of delete
as
begin
delete tab_store from deleted as d where tab_store.pid=d.pid
delete tab_people from deleted as d where tab_people.pid=d.pid
end