关于触发器的问题?初学请多多指教,可加分之200分

不胜人生一场醉 海南移动 项目总监  2003-02-23 01:45:22
/*
用途:保证在修改主代表时(即Other_Identity=‘1’)随从信息能够及时更新
*/
CREATE TRIGGER [TRIG_D_Forum_Representative_other] ON dbo.D_Forum_Representative
FOR INSERT, UPDATE
AS
Begin
IF UPDATE(Organization_Chi)
Update D_Forum_Representative Set
Organization_Chi = Organization_Chi
Where Main_Representative_ID=Representative_ID and other_identity<>'1'
意思当某一行Organization_Chi被修改时,
所有的Main_Representative_ID等于该修改行Representative_ID 值且other_identity<>'1'的Organization_Chi值都被修改为Organization_Chi

另外一个问题
删除是的触发器问题
当删除行other_identity = '1',则删除所有Main_Representative_ID=Representative_ID(该删除行值) 的值
when (other_identity = '1') then
delete from D_Forum_Representative
where Main_Representative_ID=Representative_ID
else when (other_identity <> '1')
update D_Forum_Representative
set
where Main_Representative_ID=Representative_ID and
(其它行值)Representative_ID <>Representative_ID (该行值)

...全文
63 点赞 收藏 14
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
caiyunxia 2003-02-23
deleted 中的记录就是delete的记录
将deleted当一个表即可
在UPDATE时DELETED是修改前的记录,INSERTED是修改后的记录

应该清楚了
回复
pengdali 2003-02-23
CREATE TRIGGER [TRIG_D_Forum_Representative_other] ON dbo.D_Forum_Representative
FOR delete
as

delete D_Forum_Representative where main_Representative_id in (select representative_id from deleted where ther_identity <>'1')


update D_Forum_representative set D_Forum_representative.ww=D_Forum_representative.ww - 1 from deleted a where a.representative_id=D_Forum_representative.representative_id and a.other_identity<>'1'
回复
caiyunxia 2003-02-23
以上有错,在from deleted a加 where
update from D_Forum_representative
set D_Forum_representative.ww = a.ww - 1
from deleted a where
D_Forum_Representative.main_Representative_id=
a.representative_id and .other_identity <>‘1'

回复
caiyunxia 2003-02-23
或者

delete from D_Forum_Representative
where D_Forum_Representative.main_Representative_id=
deleted.representative_id and deleted.other_identity = ‘1'
update from D_Forum_representative
set D_Forum_representative.ww = a.ww - 1
from deleted a
D_Forum_Representative.main_Representative_id=
a.representative_id and .other_identity <>‘1'
回复
caiyunxia 2003-02-23


if exists(select * from deleted where other_identity = ‘1')
delete from D_Forum_Representative
where D_Forum_Representative.main_Representative_id=
deleted.representative_id
else
update from D_Forum_representative
set D_Forum_representative.ww = a.ww - 1
from deleted a
D_Forum_Representative.main_Representative_id=
a.representative_id

回复
关于删除的触发器如何实现
现在是这样几种情况
when (deleted.other_identity = ‘1‘)
then
delete from D_Forum_Representative
where D_Forum_Representative.main_Representative_id=
deleted.representative_id
else when (deleted.other_identity <>‘1‘)
update from D_Forum_representative
set D_Forum_representative.ww = D_Forum_representative.ww - 1

回复
caiyunxia 2003-02-23
up
回复
fengzeng 2003-02-23
create TRIGGER [TRIG_D_Forum_Representative_other] ON dbo.D_Forum_Representative
FOR UPDATE
AS
Begin
IF UPDATE(Organization_Chi)
Update D_Forum_Representative Set Organization_Chi=a.Organization_Chi-b.Organization_Chi from inserted a
deleted b Where a.Main_Representative_ID=b.Representative_ID and D_Forum_Representative.Main_Representative_ID=a.Representative_ID and other_identity<>'1'
回复
pengdali 2003-02-23
CREATE TRIGGER [TRIG_D_Forum_Representative_other] ON dbo.D_Forum_Representative
FOR delete
as
update D_Forum_Representative set Main_Representative_ID=null from deleted a where a.Representative_ID=D_Forum_Representative.Main_Representative_ID
回复
pengdali 2003-02-23
create TRIGGER [TRIG_D_Forum_Representative_other] ON dbo.D_Forum_Representative
FOR INSERT
AS
Update D_Forum_Representative Set Organization_Chi=a.Organization_Chi from inserted a Where D_Forum_Representative.Main_Representative_ID=a.Representative_ID and other_identity<>'1'
回复
pengdali 2003-02-23
create TRIGGER [TRIG_D_Forum_Representative_other] ON dbo.D_Forum_Representative
FOR UPDATE
AS
Begin
IF UPDATE(Organization_Chi)
Update D_Forum_Representative Set Organization_Chi=a.Organization_Chi from inserted a Where D_Forum_Representative.Main_Representative_ID=a.Representative_ID and other_identity<>'1'
回复
fengzeng 2003-02-23
IF UPDATE(Organization_Chi)
Update D_Forum_Representative Set
Organization_Chi = a.Organization_Chi
from inserted a
Where Main_Representative_ID=a.Representative_ID and a.other_identity<>'1'


delete from D_Forum_Representative
where Main_Representative_ID in(select Representative_ID from deleted where other_identity = '1')
回复
能不能写个例子
回复
fengzeng 2003-02-23
update 时会产生两个临时表
deleted 和inserted


delete 是产生一个:deleted


insert into 是产生一个临时表 insered
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2003-02-23 01:45
社区公告
暂无公告