谢谢!可以了。用这样的约束与用触发器有什么区别?反正触发器时老有毛病。 我是这样用的触发器:create trigger tr_delete on gbook AFTER delete as delete b from answer b where exists(select * from deleted where id=b.id) -- 中间部分是你告诉我的 declare @id int,@mid int select @mid=min(id),@id=@mid-1 from deleted update gbook set id=@id,@id=@id+1 where id>@mid -- update b set id=a.id from gbook a , answer b where a.id=b.id 这样导致删除的乱七八糟
你这样更新不觉得有问题吗?update b set id=a.id from gbook a , answer b where a.id=b.id --gbook的id已经是更新后的值,这样关联,怎么可以关联到原来对应的记录?
呵呵,是有问题。我知道错哪里了。还有一个: 如果我这样做: CREATE TABLE [dbo].[gbook] ( [id] [int] IDENTITY (1, 1) NOT NULL , [uname] [varchar] (20),-- 用来存放留言人的姓名 [utitle] [varchar] (50) ,--存放标题 [ubody] [text], --留言内容 , [udate] [datetime] NULL -- 留言时间 ) ON [PRIMARY] GO CREATE TABLE [answer] ( [id] [int] NULL , [answer_body] [text] NULL , [answer_date] [datetime] NULL ) --触发器: create trigger tr_delete on gbook for delete as delete b from answer b where exists(select * from deleted where id=b.id)select identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook truncate table gbook set identity_insert gbook on insert gbook(id,uname,utitle,ubody, udate) select id, uname,utitle,ubody, udate from #t drop table #tupdate b set id=a.id from gbook a , answer b where a.id=b.id ---也就是昨天一个朋友提示写的触发器,开始没发现问题,昨天晚上突然又有点不对。 好象也是误删别的记录了。但是开始试的时候没发现,也是偶尔出现的问题? 为什么这么做不正常呢?
CREATE TABLE [dbo].[gbook] (
[id] int default dbo.f_getid() primary key ,
[uname] [varchar] (20),
[utitle] [varchar] (50) ,
[ubody] [text],
[udate] [datetime]
)
GO CREATE TABLE [answer] (
[id] [int] references [dbo].[gbook]([id]) on update cascade on delete cascade ,
[answer_body] [text] ,
[answer_date] [datetime] )
我是这样用的触发器:create trigger tr_delete on gbook
AFTER delete
as
delete b from answer b where exists(select * from deleted where id=b.id)
-- 中间部分是你告诉我的
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update gbook set id=@id,@id=@id+1 where id>@mid
--
update b set id=a.id from gbook a , answer b where a.id=b.id 这样导致删除的乱七八糟
from gbook a , answer b
where a.id=b.id --gbook的id已经是更新后的值,这样关联,怎么可以关联到原来对应的记录?
如果我这样做:
CREATE TABLE [dbo].[gbook] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[uname] [varchar] (20),-- 用来存放留言人的姓名
[utitle] [varchar] (50) ,--存放标题
[ubody] [text], --留言内容 ,
[udate] [datetime] NULL -- 留言时间
) ON [PRIMARY]
GO CREATE TABLE [answer] (
[id] [int] NULL ,
[answer_body] [text] NULL ,
[answer_date] [datetime] NULL ) --触发器:
create trigger tr_delete on gbook
for delete
as
delete b from answer b where exists(select * from deleted where id=b.id)select identity(int ,1,1) as id, uname,utitle,ubody, udate into #t from gbook
truncate table gbook
set identity_insert gbook on
insert gbook(id,uname,utitle,ubody, udate)
select id, uname,utitle,ubody, udate from #t
drop table #tupdate b set id=a.id from gbook a , answer b where a.id=b.id
---也就是昨天一个朋友提示写的触发器,开始没发现问题,昨天晚上突然又有点不对。
好象也是误删别的记录了。但是开始试的时候没发现,也是偶尔出现的问题?
为什么这么做不正常呢?