Re: Post.Office - Emerald 2.2.2

Tom Bilan ( tom@tdi.net )
Thu, 26 Jun 1997 19:18:58 -0400

>> Both of MY reasons have nothing to do with Emerald billing structure.
>> Perhaps the easiest way to accomplish my goal is to set up a trigger on the
>> calls table that inserts an identical record into the calls-keep database
>> or some such thing. I can stumble around SQL so I should be able to get
>> any info I need from there and be able to cut/paste it to a user if
necessary.
>
>Creating a trigger to move specific fields in a stop record to another
>table
>would work.
>
>> Help on such a trigger would be welcome :)
>
>Righ Click on the calls table, select triggers, and it will show you the
>current trigger. You'll need to create your second table, and then
>append
>to the trigger something like:
>
>Insert Into DupCalls(UserName, CallDate, AcctSessionTime)
>Values (inserted.UserName, Inserted.CallDate, Inserted.AcctSessionTime)
>
>The fields depend on which fields you want into the dup table.
>
>--
>Dale E. Reed Jr. (daler@iea.com)

Just for completeness here's what I did. I created a table called CALLSLOG:
CREATE TABLE dbo.callslog (
Server varchar (16) NOT NULL ,
NASPort int NOT NULL ,
CallDate smalldatetime NOT NULL ,
UserName varchar (32) NOT NULL ,
framedaddress varchar (16) NULL,
AcctSessionTime int NULL
)
GO

Then I modified the Emerald calls_insert trigger to move just the stop
records into the callslog
table:

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

UPDATE ServerPorts
SET sp.UserName = i.UserName, sp.AcctStatusType = i.AcctStatusType,
so.CallDate = i.CallDate, sp.FramedAddress = i.FramedAddress
FROM Servers s, ServerPorts sp, inserted i
WHERE s.IPAddress = i.NASIdentifier AND s.ServerID = sp.ServerID AND
sp.Port = i.NASPort

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

/* Tom's part for the callslog trigger */
INSERT callslog(server, NASPort, CallDate, UserName, FramedAddress,
ACCTSessionTime)
SELECT s.server, i.NASPort, i.CallDate, i.UserName, i.FramedAddress,
i.ACCTSessionTime
FROM Servers s, inserted i
WHERE s.IPAddress = i.NASIdentifier AND ACCTStatusType = 2
/* done */
GO

Good luck.
Tom