΢ͻӦ
WEIXINGJI YU YINGYONG
19993 18 3 vol.18 No.3



C/SϵͳݿPK/FKϵTRIGGER
һʵӦ
κɡҫϡܺ
ժҪӿɽCIMSϵͳбϵͳĿ֣SQL Server 6.5ݿб/(PK/FK)ϵʹ(TRIGGER)ڴݱݹӦá
ؼʣC/SϵͳṹѯSQL/ݹ
MS SQL Server 6.5һĶûϵݿϵͳΪͻ˿(VB)ṩ1ǿƽ̨/ϵʹʵʿʹãյϺõЧĽ1ʵʿɽCIMSѡݲɼ͹ϵͳСϵͳĿƷPK/FKϵTRIGGERʵֱһԵ⡣
1
ѡݲɼ͹ϵͳɵײݲɼվϲ㱨վɡϵͳҪײݲɼվɼ豸Ĺʱ䡢õݣʵʱ͵ϵSQL Server 6.5ݿУϲ㱨վݿгȡȤ豸¼ɱӡ
ҪSQL⽨1ݱData豸(devmark)ÿ(production)2ֶΣdevmarkΪؼ(PK)ű豸ÿɵײ㹤վÿնʱˢ¡ڴ˻Ͻ1豸Devices3ֶΣ豸(devmark)豸(devname)ÿ(production)devmarkΪؼ(PK)ͬʱҲΪDatadevmarkⲿؼ(FK)űֻʾ豸3űΪ豸Parts豸豸вļ¼7ֶΣ豸devmarkparmarkparnameÿproductionۼaccumulationdestimeٷֱpercentdevmarkparmarkΪ˫ؼ(double keys),devmarkֶҲΪDevicesdevmarkⲿؼ֡ϵͼ1ʾ

ͼ1ϵͼ
ڵĹؼά3űõһԡһڵײ㹤վˢݱDataͬʱÿݼʱݵ豸Devices豸Parts֤Devices豸Parts²ʱڵײDataҵԴûνġµСdevicesɾĳ豸ʱҪ豸partsевȫɾǰ᣻֮1豸вpartsȫɾdevicesд豸ļ¼ҲӦɾ
2ķ
2.1
1.ؼ/ؼ(PK/FK)ؼΨһ1е1ɱϣҪǿգ1Ψһؼؼֵϣؼֵƥһؼֵ1ɱϣ¼ΨһġʵϣؼֳؼγɶһĹϵؼֱǡؼֱǡͱͱաݡ
2.(TRIGGER)SQLһĴ洢̣ҲSQL Serverݿ֮һڵݱи¡ӻɾ¼ʱͻἤй涨ĲŵԶʲô޸ģǶԳڲͬݱеݵĴ޸ġַʵݱӵݹݿ⼯άƣ仯ʱֻ޸ӦTRIGGERɣϵͳά˹Чʡ
ʱ2ĲԱinserteddeleted2űڶĳinsertdeleteupdateʱڴܵӰıеĸĳinsertʱ±бͬʱӵinserted͸ôУԴ˱deleteʱдӴɾתƵdeletedУupdateЧ1delete1insert
2.2ʵӦ
ܵPK/FKϵTRIGGERʵ֡ݱ(Data)豸(Devices)豸(Parts)֮ݹ
1.Ϊ˱֤ÿdataݸºʱdevicespartsݣdatadevicesϷֱupdate£
tri1:create trigger updatedevices
 on data for update
 as
Update devices
Set devices.production=inserted.production from inserted
Where devices.devmark=inserted.devmark
tri2:create trigger updateparts
 on devices for update
 as
 update parts
 set parts.production=inserted.production from inserted
 where parts.devmark=inserted.devmark
2֤dataĳ豸ÿ(production)ˢº󣬰豸(devmark)(production)devicesparts֮partsд1updateۼٷֱȣ
tri3:create trigger calculate
 on parts for update
as
update parts
set parts.accumulation=parts.accumulation+parts.production
parts.percent=(parts.accumulation+parts.production)/parts.destime from inserted
where parts.devmark=inserted.devmark
2.devicesĳ豸ʱdatadevicesPK/FKչϵSQL Server 6.5Ҫdataе豸¼򣬻ؾinsertͬpartsĳĲʱҲpartsdevicesPK/FKչϵǿֻdevicesе豸ĲǿԵ/ϵҲSQL Server 6.5ڰ汾֮һӲͲõġµС
3.devicesɾĳ豸ʱ豸partsдڲ¼datadevicesPK/FKչϵSQL Server 6.5ɾͱյġСpartsаѴ豸в¼ɾɾdevicesе̨豸ļ¼Ϊˣpartsм1deleteĿǵpartsĳ豸вɾ֮ͬɾdevicesеĸ豸¼
tri4:Create trigger deleteparts
on parts for delete
as
if(select count(*)from parts,deleted where parts.devmark=deleted.devmark)=0
begin
delete devices from deleted
where devices.devmark=deleted.devmark
end
3ű޸ġ롢ɾȲݹͼ2ʾ 


ͼݹϵͼ
3
ͨʵϵͳĿƷ֪ܽϺõô͹ϵݿıPK/FKϵTRIGGERʵֹϵݱ֮ݹַǿĹܣʵӦȡ°빦Ч
ߵλϴѧϢϵ(410082)
ο
1Microsoft.Microsoft SQL Server 6.5 for Windows NTֲ().ѧԷ磬1995
2Microsoft.Microsoft SQL Server 6.5Աָ.磬1998
3Microsoft.Microsoft SQL Server 6.5 Transact-SqlԲοֲ.磬1998
(ոڣ1998-09-10)
