We have modified the trigger in the calls table to solve our problem with
the port calculation of some NASes like CISCO AS58xx. You only need to add a
NAS with ONE port. If a call comes in, the trigger now checks if the port
exist. If not it looks for the NAS ID via NAS IP-address and creates the new
port. So if you have "funny" port numbers, or don't want to create LOTS of
ports manually you can try out this trigger. It works fine for us with CISCO
AS52xx 52xx and 58xx and 3COM RAS1500 or Bay Remote-Annex 2000.
For questions just mail to
GZebedin@net4you.net
<---- snip
if exists (select * from sysobjects where id = object_id('dbo.calls_insert')
and sysstat & 0xf = 8)
drop trigger dbo.calls_insert
GO
CREATE TRIGGER calls_insert ON dbo.Calls
FOR INSERT
AS
declare @anz int
Select @anz=count(sp.Port) FROM Servers s, ServerPorts sp, inserted i
WHERE s.IPAddress = i.NASIdentifier AND
s.ServerID = sp.ServerID AND sp.Port = i.NASPort
if (@anz = 0)
BEGIN
insert into ServerPorts (ServerID, Port) Select s.ServerID, y.NASPort
FROM Servers s, inserted y
WHERE s.IPAddress = y.NASIdentifier
END
UPDATE ServerPorts
Set sp.Username = i.Username,
sp.AcctStatusType = i.AcctStatusType,
sp.CallDate = DateAdd(Second, 0-i.AcctDelayTime, i.CallDate),
sp.FramedAddress = i.FramedAddress,
sp.CallerID = i.CallerID,
sp.ConnectInfo = i.ConnectInfo,
sp.AcctSessionID = i.AcctSessionID
FROM Servers s, ServerPorts sp, inserted i
WHERE s.IPAddress = i.NASIdentifier AND
s.ServerID = sp.ServerID AND
sp.Port = i.NASPort AND
(DateAdd(Second, 0-i.AcctDelayTime, i.CallDate) >= sp.CallDate OR
sp.CallDate IS NULL)
UPDATE SubAccounts
Set sa.TimeLeft = sa.TimeLeft - (i.AcctSessionTime/60 + 1)
FROM SubAccounts sa, inserted i
WHERE sa.Login = i.Username
and sa.TimeLeft <> NULL
and i.AcctStatusType = 2
GO
For more information about this list, including removal,
please see http://www.iea-software.com/maillist.html