Re: Consolidation

Dale E. Reed Jr. ( (no email) )
Thu, 26 Feb 1998 13:34:33 -0800

iml@interconnect.net wrote:
>
> Dale if I wanted to add AcctInputOctets and Output and
> AcctTerminateCause would I just add those to the create table section
> and that's it?? I don't have to edit anything in your script below
> that do I?

You'd have to modify the trigger to copy those fields as well:

> >> >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

The above select must include all the fields, IN ORDER, of the CallDetails
table.

> >> > From inserted i
> >> > Where i.UserName is not NULL and i.UserName <> 'NULL'
> >> > END
> >> >
> >> >
> >> > UPDATE Emerald..SubAccounts
> >> > Set sa.TimeLeft = sa.TimeLeft - (i.AcctSessionTime/60 + 1)
> >> > FROM Emerald..SubAccounts sa, inserted i
> >> > WHERE sa.login = i.UserName
> >> > and sa.TimeLeft <> NULL
> >> > and i.AcctStatusType = 2
> >> >GO

-- Dale E. Reed Jr.  (daler@iea-software.com)_________________________________________________________________       IEA Software, Inc.      |  RadiusNT, Emerald, and NT FAQs Internet Solutions for Today  |   http://www.iea-software.com