Mikel
由于需要准确控制在线人数，但是很难捕获用户关闭浏览器的事件来清理online表中的在线用户记录，造成在线人数不准，利用global.asa的session_onEnd处理，又不能及时清除，只有在timeout和session被清空时才触发,没办法只能在数据表online加入Inser，Update触发器清除定时30分钟后的记录来保持在线人session超时后有人上线或更新online的date时间后删除超时的online记录。
OnLine表结构
<span class="pun">******</span><span class="pln"> </span><span class="pun">对象:</span><span class="pln"> </span><span class="typ">Table</span><span class="pln"> </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="pln">online</span><span class="pun">]</span><span class="pln"> </span><span class="pun">脚本日期:</span><span class="pln"> </span><span class="lit">07</span><span class="pun">/</span><span class="lit">02</span><span class="pun">/</span><span class="lit">2008</span><span class="pln"> </span><span class="lit">09</span><span class="pun">:</span><span class="lit">43</span><span class="pun">:</span><span class="lit">10</span><span class="pln"> </span><span class="pun">******/</span><span class="pln">SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO</span><span class="typ">Cr</span><span class="pun">&</span><span class="com">#101;ate TABLE [dbo].[online](</span><span class="pln"></span><span class="pun">[</span><span class="pln">id</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="kwd">int</span><span class="pun">]</span><span class="pln"> IDENTITY</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln"></span><span class="pun">[</span><span class="pln">compid</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="kwd">int</span><span class="pun">]</span><span class="pln"> NULL CONSTRAINT </span><span class="pun">[</span><span class="pln">DF_online_compid</span><span class="pun">]</span><span class="pln"> DEFAULT </span><span class="pun">((</span><span class="lit">0</span><span class="pun">)),</span><span class="pln"></span><span class="pun">[</span><span class="pln">employeeId</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="kwd">int</span><span class="pun">]</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"></span><span class="pun">[</span><span class="pln">sdate</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datetime</span><span class="pun">]</span><span class="pln"> NULL CONSTRAINT </span><span class="pun">[</span><span class="pln">DF_online_sdate</span><span class="pun">]</span><span class="pln"> DEFAULT </span><span class="pun">(</span><span class="pln">getdate</span><span class="pun">()),</span><span class="pln"></span><span class="pun">[</span><span class="pln">online</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="kwd">int</span><span class="pun">]</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln">CONSTRAINT </span><span class="pun">[</span><span class="pln">PK_online</span><span class="pun">]</span><span class="pln"> PRIMARY KEY CLUSTERED</span><span class="pun">(</span><span class="pln"></span><span class="pun">[</span><span class="pln">id</span><span class="pun">]</span><span class="pln"> ASC</span><span class="pun">)</span><span class="pln">WITH </span><span class="pun">(</span><span class="pln">PAD_INDEX </span><span class="pun">=</span><span class="pln"> OFF</span><span class="pun">,</span><span class="pln"> STATISTICS_NORECOMPUTE </span><span class="pun">=</span><span class="pln"> OFF</span><span class="pun">,</span><span class="pln"> IGNORE_DUP_KEY </span><span class="pun">=</span><span class="pln"> OFF</span><span class="pun">,</span><span class="pln"> ALLOW_ROW_LOCKS </span><span class="pun">=</span><span class="pln"> ON</span><span class="pun">,</span><span class="pln"> ALLOW_PAGE_LOCKS </span><span class="pun">=</span><span class="pln"> ON</span><span class="pun">)</span><span class="pln"> ON </span><span class="pun">[</span><span class="pln">PRIMARY</span><span class="pun">]</span><span class="pln"></span><span class="pun">)</span><span class="pln"> ON </span><span class="pun">[</span><span class="pln">PRIMARY</span><span class="pun">]</span> |
页面每次进入或刷新时判断sdate是否超过30分钟，如果超过更新sdate的时间为当前最新时间，这样保持在线时间一直是最新的时间，避免被删除
<span class="pun">&</span><span class="com">#39;判断用户登录状态</span><span class="pln"></span><span class="kwd">if</span><span class="pln"> session</span><span class="pun">(</span><span class="str">"checklogin"</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">then</span><span class="pln"></span><span class="kwd">if</span><span class="pln"> session</span><span class="pun">(</span><span class="str">"zjzxid"</span><span class="pun">)<></span><span class="lit">0</span><span class="pln"> </span><span class="kwd">then</span><span class="pln">sessioncompid</span><span class="pun">=</span><span class="pln">session</span><span class="pun">(</span><span class="str">"zjzxid"</span><span class="pun">)</span><span class="pln"></span><span class="kwd">else</span><span class="pln">sessioncompid</span><span class="pun">=</span><span class="pln">session</span><span class="pun">(</span><span class="str">"compid"</span><span class="pun">)</span><span class="pln"></span><span class="kwd">end</span><span class="pln"> </span><span class="kwd">if</span><span class="pln">employee</span><span class="pun">=</span><span class="pln">session</span><span class="pun">(</span><span class="str">"employee"</span><span class="pun">)</span><span class="pln"></span><span class="kwd">set</span><span class="pln"> online</span><span class="pun">=</span><span class="pln">server</span><span class="pun">.</span><span class="pln">cr</span><span class="pun">&</span><span class="com">#101;ateobject("adodb.recordset")</span><span class="pln"></span><span class="pun">&</span><span class="com">#39;跟数据表中暂存的用户ID进行判断，其中sessioncompid中存储的是用户ID</span><span class="pln">online</span><span class="pun">.</span><span class="pln">open </span><span class="str">"select id,compid,sdate from online where compid="</span><span class="pun">&</span><span class="pln">sessioncompid</span><span class="pun">&</span><span class="str">" and employeeid="</span><span class="pun">&</span><span class="pln">employee</span><span class="pun">,</span><span class="pln">conn</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pln"></span><span class="kwd">if</span><span class="pln"> online</span><span class="pun">.</span><span class="pln">recordcount</span><span class="pun"><></span><span class="lit">0</span><span class="pln"> </span><span class="kwd">then</span><span class="pln"></span><span class="pun">&</span><span class="com">#39;如果表中有该用户的话，就判断存储时间是否超出设置的超时时间</span><span class="pln"></span><span class="pun">&</span><span class="com">#39;如果超时，那么将系统时间赋上，以保证当前的用户的状态</span><span class="pln">oldid</span><span class="pun">=</span><span class="pln">online</span><span class="pun">(</span><span class="str">"id"</span><span class="pun">)</span><span class="pln"></span><span class="kwd">if</span><span class="pln"> DATEDIFF</span><span class="pun">(</span><span class="str">"n"</span><span class="pun">,</span><span class="pln">online</span><span class="pun">(</span><span class="str">"sdate"</span><span class="pun">),</span><span class="pln">now</span><span class="pun">())></span><span class="lit">30</span><span class="pln"> </span><span class="kwd">then</span><span class="pln">sql</span><span class="pun">=</span><span class="str">"update online set sdate=getdate() where id="</span><span class="pun">&</span><span class="pln">oldidconn</span><span class="pun">.</span><span class="pln">execute sql</span><span class="kwd">end</span><span class="pln"> </span><span class="kwd">if</span><span class="pln"></span><span class="kwd">else</span><span class="pln"></span><span class="pun">&</span><span class="com">#39;如果表中没有该用户，则进行添加操作</span><span class="pln">sql</span><span class="pun">=</span><span class="str">"insert into online (compid) values ("</span><span class="pun">&</span><span class="pln">sessioncompid</span><span class="pun">&</span><span class="str">")"</span><span class="pln">conn</span><span class="pun">.</span><span class="pln">execute sql</span><span class="kwd">end</span><span class="pln"> </span><span class="kwd">if</span><span class="pln">online</span><span class="pun">.</span><span class="pln">close</span><span class="kwd">set</span><span class="pln"> online</span><span class="pun">=</span><span class="pln">nothing</span><span class="kwd">end</span><span class="pln"> </span><span class="kwd">if</span> |
触发器
<span class="typ">Alt</span><span class="pun">&</span><span class="com">#101;r TRIGGER [dbo].[clearOnline]</span><span class="pln">ON </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="pln">online</span><span class="pun">]</span><span class="pln">AFTER </span><span class="typ">Ins</span><span class="pun">&</span><span class="com">#101;rt,Update</span><span class="pln">AS</span><span class="kwd">BEGIN</span><span class="pln"></span><span class="pun">--</span><span class="pln"> SET NOCOUNT ON added to prevent extra result sets </span><span class="kwd">from</span><span class="pln"></span><span class="pun">--</span><span class="pln"> interfering </span><span class="kwd">with</span><span class="pln"> </span><span class="typ">Sel</span><span class="pun">&</span><span class="com">#101;ct statements.</span><span class="pln">SET NOCOUNT ON</span><span class="pun">;</span><span class="pln"></span><span class="pun">--</span><span class="pln"> </span><span class="typ">Ins</span><span class="pun">&</span><span class="com">#101;rt statements for trigger here</span><span class="pln"></span><span class="typ">Del</span><span class="pun">&</span><span class="com">#101;te online where datediff(minute,sdate,getdate())>30</span><span class="pln"></span><span class="kwd">END</span> |
