subject_51912.htm

vc

<p>
序号：51912 发表者：jamesgu2008 发表日期：2003-09-05 14:36:00
<br>主题：触发器请教－小叶请看
<br>内容：&nbsp;&nbsp; 我想把固定资产fixed表中凡是做过变更的的记录的ID、FianID,变更的字段和变更日期通过触发器记录到一个新表中。我的思路是这样的：fixed表中需要更新的字段有description varchar(20)、curuser varchar(50)、Newlocation varchar(50)、status char(3)和buydate datetime 5个字段，我想建5个触发器，分别对于这5列，当有记录变更时，把变更内容插入相应的new表中，最后再根据ID号相同，从这5个new表中形成一个反馈给财务的变更记录的视图alter_fixed.<BR><BR>&nbsp;&nbsp; fixed表中buydate字段对应的触发器如下：<BR>CREATE TRIGGER tri_new_buydate ON [dbo].[fixed] <BR>FOR UPDATE AS<BR>if update(buydate)<BR>&nbsp;&nbsp;&nbsp;&nbsp;begin<BR>&nbsp;&nbsp;&nbsp;&nbsp;delete new_buydate from deleted a,new_buydate b<BR>&nbsp;&nbsp;&nbsp;&nbsp;where a.[ID]=b.[ID]<BR>&nbsp;&nbsp;&nbsp;&nbsp;if @@error!=0<BR>&nbsp;&nbsp;&nbsp;&nbsp;goto error<BR>&nbsp;&nbsp;&nbsp;&nbsp;end<BR>&nbsp;&nbsp;&nbsp;&nbsp;if (select count(*) from inserted)&gt;0<BR>&nbsp;&nbsp;&nbsp;&nbsp;begin<BR>&nbsp;&nbsp;&nbsp;&nbsp;insert new_buydate<BR>&nbsp;&nbsp;&nbsp;&nbsp;select [ID],finanID,buydate ,getdate() from inserted<BR>&nbsp;&nbsp;&nbsp;&nbsp;if @@error!=0<BR>&nbsp;&nbsp;&nbsp;&nbsp;goto error<BR>&nbsp;&nbsp;&nbsp;&nbsp;end<BR>return<BR>error:<BR>rollback transaction<BR>return<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;fixed表中description字段对应的触发器如下：<BR>CREATE TRIGGER tri_new_desc ON [dbo].[fixed] <BR>FOR UPDATE AS<BR>if update(description)<BR>&nbsp;&nbsp;&nbsp;&nbsp;begin<BR>&nbsp;&nbsp;&nbsp;&nbsp;delete new_desc from deleted a,new_desc b<BR>&nbsp;&nbsp;&nbsp;&nbsp;where a.[ID]=b.[ID]<BR>&nbsp;&nbsp;&nbsp;&nbsp;if @@error!=0<BR>&nbsp;&nbsp;&nbsp;&nbsp;goto error<BR>&nbsp;&nbsp;&nbsp;&nbsp;end<BR>&nbsp;&nbsp;&nbsp;&nbsp;if (select count(*) from inserted)&gt;0<BR>&nbsp;&nbsp;&nbsp;&nbsp;begin<BR>&nbsp;&nbsp;&nbsp;&nbsp;insert new_desc<BR>&nbsp;&nbsp;&nbsp;&nbsp;select [ID],finanID,description,getdate() from inserted<BR>&nbsp;&nbsp;&nbsp;&nbsp;if @@error!=0<BR>&nbsp;&nbsp;&nbsp;&nbsp;goto error<BR>&nbsp;&nbsp;&nbsp;&nbsp;end<BR>return<BR>error:<BR>rollback transaction<BR>return<BR>&nbsp;&nbsp;&nbsp;&nbsp;其他3个字段的触发器依此类推。但现在问题出现了，当我更新fixed表中1条记录的buydate字段时(用update语句）,new_buydate表中增加了该条记录（正常），但同时new_desc表中也增加了该条记录(不正常),如果我再把剩下的3个触发器写完，那就会用在new_curuser、new_location、new_status表中分别无故又增加1条记录。<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;请问，如果进行修改触发器定义，才能做到当修改buydate字段时,只在new_buydate表中增加记录，其他的new表不增加记录?<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR>2003-9-5 14:46:05