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

[python与大数据分析]

/*
用途：保证在修改主代表时（即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 (该行值)

[fengzeng]

update 时会产生两个临时表
deleted 和inserted


delete 是产生一个：deleted


insert into 是产生一个临时表 insered

[python与大数据分析]

能不能写个例子

[fengzeng]

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')

[pengdali]

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'

[pengdali]

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]

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

[fengzeng]

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'

[caiyunxia]

up

[python与大数据分析]

关于删除的触发器如何实现
现在是这样几种情况
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]

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

[caiyunxia]

或者

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]

以上有错，在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'

[pengdali]

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]

deleted 中的记录就是delete的记录
将deleted当一个表即可
在UPDATE时DELETED是修改前的记录，INSERTED是修改后的记录

应该清楚了