CallDetails

Michael Whisenant ( mwhisen@airnet.net )
Fri, 18 Sep 1998 08:57:41 -0500

Dale,

This is a posting that you made and I have a few questions regarding the
CallDetails that may clear up some points. From the first portion it
creates the table inside Emerald and
populates the fields. Since this is a portion of the information contained
in calls the database size should not grow extremely large. However, if
you wanted to place this into another database what would the process be,
and/or advantages of doing so?

I have a question on the trigger and exactly when it is used. Are the
records inserted each time the calls arrive? Or could this be scheduled to
run as a scheduled task nightly?

If you understand SQL Server, it should be fairly simple to add the
following to create the paralell table.
----------------------------------------------------------------
Use Emerald Go
/****** Object: Table dbo.CallDetails Script Date: 12/8/97 1:03:50 PM
******/ CREATE TABLE CallDetails ( CallDate smalldatetime NOT NULL ,
UserName varchar (18) NOT NULL , AcctSessionTime int NULL , FramedAddress
varchar (16) NULL , NASIdentifier varchar (16) NULL , CallerID varchar (12)
NULL ) GO
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 @ast int
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.ConnectInfo =
i.ConnectInfo , sp.CallerID = i.CallerID FROM Servers s, ServerPorts sp,
inserted i WHERE s.IPAddress = i.NASIdentifier AND s.ServerID = sp.ServerID
AND sp.Port = i.NASPort AND (sp.CallDate <= DateAdd(Second,
0-i.AcctDelayTime, i.CallDate) OR sp.CallDate IS NULL)
Select @ast=i.AcctStatusType From inserted i IF @ast = 2 BEGIN Insert INTO
CallDetails Select DateAdd(Second, 0-i.AcctDelayTime, i.CallDate),
i.UserName, i.AcctSessionTime, i.FramedAddress, i.NASIdentifier, i.CallerID