[Emerald] On-Insert Trigger in CALLS solves NAS-Port troubles

Gerald Zebedin ( GZebedin@net4you.net )
Sat, 27 Mar 1999 21:02:13 +0100

Hello,

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