Here's the whole trigger. You need to make a table called CALLSLOG or whatever you want to name it.
You can pick which things you want to send, just follow the example.
Good luck.
Tom
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
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
GO
-----Original Message-----
From: Will LaSala [SMTP:will@greennet.net]
Sent: Thursday, July 31, 1997 12:31 PM
To: emerald@emerald.iea.com
Subject: Re: Anyone have any tips to speed up SQL querys?
Tom,
I'd love to see this trigger 'cause I hate losing my calls history
everytime I have to consolidate!
Plus I lose the info for my customers when they want to check there call
history.
Thanks
Will ,,,=^. .^=,,,
Webmaster
* GreenNet========================================*
* "Link Locally ~ Surf Globally" http://www.greennet.net *
* Come Play On Our Game Server *
* Serving northeastern Massachusetts, southern New Hampshire *
* TEL: 508-363-8898 FAX: 508-363-1225 email:will@greennet.net*
*===============================================*
-----Original Message-----
From: Tom Bilan <tom@tdi.net>
To: emerald@emerald.iea.com <emerald@emerald.iea.com>
Date: Thursday, July 31, 1997 12:26 PM
Subject: RE: Anyone have any tips to speed up SQL querys?
>I created a trigger for inserting on the CALLS table that inserts just
AcctStatusType = 2
>messages and a limited number of fields (5 I think) so that the data was
small and fast.
>I now can consolidate without losing my call history and I run reports off
of that table
>instead of the larger CALLS table.
>
>Also, you can move your tempdb into RAM but you need to have the extra
hardware.
>
>Also, use the fastest drive you can possible find. I use an Ultra/Wide
Barracuda for
>my database drive and things seem to chug along relatively smoothly.
>
>My .02
>
>Tom
>
>P.S. Let me know if you want the trigger, I'll post it.
>
>-----Original Message-----
>From: Mike Miller [SMTP:michael@abraxis.com]
>Sent: Thursday, July 31, 1997 12:02 PM
>To: emerald@emerald.iea.com
>Subject: Anyone have any tips to speed up SQL querys?
>
>Hello all:
>
>Does anyone have any tips to speed up SQL querys? I have set up a query
>in MSQuery to assist us in tracking down spammers. Basically it takes
>an IP address and lists all logins/logouts for that address during a
>specified time period. The problem is it takes about 10 to 15 minutes
>to generate the report. I want to incorporate the query into a web
>based idc, but attempting to do so results in a timeout while waiting on
>the results from the server.
>
>The query:
>
>SELECT Calls.CallDate, Calls.UserName, Calls.AcctStatusType
>FROM "Emerald2.dbo".Calls Calls
>WHERE (Calls.CallDate>={ts '1997-07-30 00:00:00'} And Calls.CallDate<{ts
>'1997-07-31 00:00:00'}) AND (Calls.FramedAddress='206.155.199.52')
>ORDER BY Calls.CallDate
>
>Emerald configuration:
>
>System: NT Server 4.0, Pentium 133, 24 MB RAM
>The operating system and SQL server are on one SCSI hard disk, while the
>SQL database is on another SCSI hard disk to increase speed.
>
>Emerald has been running for about 2 months. We have not consolidated
>as of yet as I was hoping to keep at least a month of records for this
>purpose. Our user base is around 1000. Emerald is being used for
>accounting only on most of our ports, however is used for authentication
>and accounting on a handful of our X2 ports. The database is currently
>using about 76MB of space to store the records.
>
>Does anyone have any suggestions to speed up this query and/or our
>Emerald server?
>
>
>
>
>--
>=================================================
> /\ Mike A. Miller
> /--\ \/ Abraxis Networks
>/ \ B R A /\ I S
> /
> N E T W O R K S michael@abraxis.com
>=================================================
>
> ----------------------------------------------------------
> Emerald Mailing List listserver@emerald.iea.com
>
----------------------------------------------------------
Emerald Mailing List listserver@emerald.iea.com
------ =_NextPart_000_01BC9DB1.5F9E18A0
Content-Type: application/ms-tnef
Content-Transfer-Encoding: base64
eJ8+IjsQAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy
b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAwAEAAAEAAAAQAAAAAwAAMAIAAAAL
AA8OAAAAAAIB/w8BAAAATQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAGVtZXJhbGRAZW1lcmFs
ZC5pZWEuY29tAFNNVFAAZW1lcmFsZEBlbWVyYWxkLmllYS5jb20AAAAAHgACMAEAAAAFAAAAU01U
UAAAAAAeAAMwAQAAABgAAABlbWVyYWxkQGVtZXJhbGQuaWVhLmNvbQADABUMAQAAAAMA/g8GAAAA
HgABMAEAAAAaAAAAJ2VtZXJhbGRAZW1lcmFsZC5pZWEuY29tJwAAAAIBCzABAAAAHQAAAFNNVFA6
RU1FUkFMREBFTUVSQUxELklFQS5DT00AAAAAAwAAOQAAAAALAEA6AQAAAB4A9l8BAAAAGAAAAGVt
ZXJhbGRAZW1lcmFsZC5pZWEuY29tAAIB918BAAAATQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAA
AGVtZXJhbGRAZW1lcmFsZC5pZWEuY29tAFNNVFAAZW1lcmFsZEBlbWVyYWxkLmllYS5jb20AAAAA
AwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAALpXwEEgAEAMQAAAFJFOiBBbnlvbmUgaGF2
ZSBhbnkgdGlwcyB0byBzcGVlZCB1cCBTUUwgcXVlcnlzPwC4EAEFgAMADgAAAM0HBwAfAAwAOQA6
AAQAfQEBIIADAA4AAADNBwcAHwAMADkABwAEAEoBAQmAAQAhAAAARDNBQzhDMzc5ODA5RDExMTlC
RkQwMDYwOTc3QjI4OUMAKwcBA5AGAPgOAAAhAAAACwACAAEAAAALACMAAAAAAAMAJgAAAAAACwAp
AAAAAAADAC4AAAAAAAMANgAAAAAAQAA5AMDU5+XSnbwBHgBwAAEAAAAxAAAAUkU6IEFueW9uZSBo
YXZlIGFueSB0aXBzIHRvIHNwZWVkIHVwIFNRTCBxdWVyeXM/AAAAAAIBcQABAAAAFgAAAAG8ndLl
5zeMrNQJmBHRm/0AYJd7KJwAAB4AHgwBAAAABQAAAFNNVFAAAAAAHgAfDAEAAAAMAAAAdG9tQHRk
aS5uZXQAAwAGEGR5QdoDAAcQDQ8AAB4ACBABAAAAZQAAAEhFUkVTVEhFV0hPTEVUUklHR0VSWU9V
TkVFRFRPTUFLRUFUQUJMRUNBTExFRENBTExTTE9HT1JXSEFURVZFUllPVVdBTlRUT05BTUVJVFlP
VUNBTlBJQ0tXSElDSFRISU5HU1kAAAAAAgEJEAEAAADJCwAAxQsAAGsXAABMWkZ1af4KGncACgED
AfcgAqQD4wIAY4JoCsBzZXQwIAcThwKDAFAO9nBycTIP9iZ9CoAIyCA7CW8yNWY1AoAKgXVjAFAL
A2MDAEELYG5nMTAzM0kLpiBIBJBlJwQgdGBoZSB3aAbwFvB0CQUQZ2cEkC4gIFkpCGAgbgngZBbA
byB1AMBrFvBhFsABoBdBYwcHQBdAGIBDQUxMUzBMT0cgBbEXEGF0pGV2BJAgeRghdwBwEwVAGKFu
YQeAIGl0Pi4KogqAGBIZoAOgcGlsY2sXAR2QaBbBC4BnBwQgG2oPsG5kLCBq3HVzBUACEBnAbwfg
FtJsZXgcMAtQZRyVHKRHum8EcCAKQB2gITtUA3CvIUoGkCCxBAB0BCAoD7B1F0BjBUAqIAADYR9Q
efBzb2JqJTEEIBcQFnFbHGAYgD0akCYjXybwKNQnZAbgLhmicyegAIDxBJB0JykZEB+AJdIf4MEa
8CAmIDB4JGAnIEw4KSFFAZEgZANgcC8XZisgKA0hpU8hSkNSAEVBVEUgVFJJ8EdHRVIZkyh2CuMK
gDRPTivzQyhCL/VGT9EvEElOUy8AVA/wBfBxIUpVUEQucgZhGyFQDwkRMRYx8DIgc3AuVe0osU4c
MicgaTUnH6A1ATRBYyVAUymhH9BUed5wNaQ23DaBMLREGvE1sxM5NjaDRnIcMWRBZD8rMAeQBCA1
wjrcMTVST/ZNM5UEIHMfoDOqNQAfoMsolBhxaS/1V0gvAC6QUHMuSVA7Ok4yQEmfAQACMAaQCJEP
8E5EQJH9M6RJQqAnIDUBQudCgzUQnzQCQVZEwzJ/M4F1YjbRpwhgAjA0S2EuB2JMAXG7Q2JIeS0k
4DflO3FpAiCJB2IvNg/gKyAxKQB/PNpHGUhgPx9AJ0hwCQBnTwuANbspFEh5PD4HsFXvGjApEzfu
JxEyIUox1Sgz/08hJPEzwh+gRVUfoDoINgjDOtsfoEFDQ1RKmSp1+THwTEVYcECRVRZFN1qi/zoI
Neo73VqiWF083z3jTY//QE9BX0JkWFNSjy1OIUgLMHhsaTNLUBUBAUARQG9nGwAlQBCEMTZJ8GjC
T98XgQuAB0AF0DtxYRewaMMfIUZn1GehCxNn1mktMYw0NAFAZyAxODABQF8M0GxjKxA60ANwOgyD
Ys0P4FcDEAMgTGEGEAtggCBbU01UUDoD8L0ZwEAJwxhQHIBwQV0hRU8rEAZgAjBt91RoCHBzCGRh
eR+gSnVseQggMzEfoDE5OTeJS5AyOnMQIFBNcMcbI2Bt92UHgDrgbGRA/XVlLgiQSHAFoCOGcTFH
EKMmMm33UmU6D/BuG2B9GFAgD4AbICkRcvBCEHAvFrEfQTeQGHF1K2BTUdVRsHEKUHIl8D9qb2t6
T2ckC7Ycs3ShbSx+BUn7J+AiQG95MR8zF1Ed4Bax3SuVJxmgH9AW8El5ATmR/wkAAJAVcBjAcvAo
M3kAJKHtBbB5HKQbEnlCEBxBgWK/f4QFoACABvAm8BrxIRykflAKQAQggWCB0YACHFFu/wIQIAEF
wIJCH9EDcD3CJpH/A6AW0XLwG6YPcAWQHbAW0T8mwRmiHKSC1SKcD4Buay8PQGE0bpKNIyyNgD1e
MRfgLl49jYFhJWVi3wDAH+AEkBykJXBHCdIHwFx0PZBPkV+RsyqPRiJmTAuAHbBMbxmicvAgVn4X
8EcAciRgRwkAYn2T4iKNIhUDctB5AAJAcNA6Ly93loAub/pnc/9y0A/gmAiSxwhQHEGGAHKA9CBP
A6BPCHCPsBwyM6TvmAmYqDOiggJuCREW4I7z/QOgTY7wSGAPcIEhAkA+AV0mAHWJ4gOgB8JIIOFz
Lx3gJsGYqC6ATHiANTAEOC1nQDMtODg54jiNIUZBWKDYc6AUAOt1UQtwbG+fdJLGkb+l/z+R/yF3
aM9p3W2zfmIgQosDEAORPIfxQHRkNeD9cEE+IwZ4gHVvdnRRQK0vv3Z0rHU5cqrxcj9zRzJooPd0
BndmB/BFeI95n3qvZf/9CoA+gWAFAHZwYMIZISuV/4dSKJSCAgIgFsMaExk1FtB/KbEolAQgH8Ic
pFI/U0U+vweBadIEICkiGSBnIG0ccNkYcW51BtAbMW8kYEIx368AJNGi4IFgHjJrKQAmAL+7tBbS
hVEZIBugjHVzAMD7bqEpImaO8RyVuGGc0Afg/x1ChOkXABxwFyCesIHNgsabKROBYHJHcMigZXA0
E/3AoGYcpMChwjQZUrgFKJH9GwBhGIDKA4GxCsArwrsJ68SGuAVBKGBvH6AbYh1C/wRgtLEbYQXA
GwAg8CfwYGHpGKFSQT1gYsaxG2IYVveEZCCTF3BhioULERugCXD/zV/OYoEiFtLEQgeQBUArMP5p
z0QdNIHgAJAZYkIwH4BPF+GBYIEiA5FVbNJxL/dugAEAHKRCCsA64IfAsUD/h0K+VnLwwsKU8IEx
1dQpIu8eNX/hJcCJU3WCIAdAAiD/giAJcAtgQhAbIHLhw6AiEJcW0HLg041NcvAuML5G97gFI2fN
xlBC0BfgSNAFQP0cQWvFIiRRG2gW4Rd1H6D/fyBuodaxu/HEd7gFqL9p3fI+qrRNaRjx6LAZwQXA
729Ev9APcSUQQAGgOuAkkft2knC1PnFTsO9zKd/gc/f/4NGtD3Zc63CzZrQvtT+2Tj/NxhZgIDHd
EaNA05xEb/+/I/Ev8j965teS9uMPsfLS7xkgevPK1wXRUXsCGJKdsV8kodTBYGEXYQDQa4ICZP8g
UAOgNQAcMIgSF+HZQACQ35PUHHAZMRjwjHU+A5FiAH8ZEDtFKSJnICSyw8JPI3N+L08hnqEEILmy
u8MARmTXCHGCEdKVPvfxY0IiGIH/hAI3kNtQIiAX4evgBWErQP8ZYSXAgEH+9hkQKADGsRWQ/RiS
MaLgv9DAMNWBuAUYof8XsBhQOuA5kRbSyPTXgxum++bQR1ByyREJ5nrz0CTC8c+OwLgF2vJg4WRj
H6DQwv8a8M/CukMYof1QwfI7YXLQ/yTBT1EZIUihxqId0RdBG6D/HHC6VAj2CjMQVCWTFtJVFP/T
jQYSDJP1PVm1MNM5+ReEnzYIF4S9POfmPUIiRe6k9DIuJ/EiMMUw07gFYYRCKBeMPj1ceyTBJ+lz
Ui0wH5Azl/Bs8O2AcSAiJ1x98PH4MBeMPP8fArgFH1hz4CAZKQBCgh31STrbPSd94DYuCFA14yYQ
c2AuNTIo8LgFMaDyRC8BQlkXfdOcG5WE0v1CMGcDsN2xSuAWLnswz8Ft8OBOMiCapTTf0DjgUCdB
8sBAS5AzMzjgMjTt+zBC0HIVKW8Fgd2xghH+cyvz25PzElUUv4CKEbqR+faiU0NBwPbRa4ADkPwg
/ms44BGEnQHrFrZB2reAQf+6keOA3mG5gTIcC1W4oYEx//fz040ppgLgPLDAYIiByLF+boICubIH
lO2g3kDroGj//hKOsPbUNVGE2muR/5TJUv/PYOJROUEK8oKxL2APZQdQ/8kADuF/UJ0iv4E7A8CS
j9DfC7HBEQKU/CC4BXADsNax/9NwmwCaEoEh6VA0lmbgR2H/+DAH8Gzw14EppoBBwGCCAv+BIcQh
2fed4EzTulRy4bqR/95A5QHAoc+CyROecMaQDWD/mtKAQUX2v4Cesi1xx4Aqsp//d7jxRvq/gYwx
ZGaV4f1IZVjtoMkTBeU0WtmQ2WA/LWFy4LgFvKAD0wejNzb3LlHAof2hY3+UgwEKBkED/9ON9i/3
MhBQuWHlAF6h96z3gCMMlMhRL7nBSJEpPRRE//O9Wn/mBLgFpt9dz17NuAVxmwAvXFybCmEs6LNB
d43Q6QRf1i9qQWCIYHAvzWEsQepknxF0d7nAjGZ+PmSTYHEuYSQwYES4cFPvX9ebCWBgaGpORNAG
ADuA+5nQZ4FLcUCbB+nf4Pdez/dun17v07og5kNx33Lvc///58dE152gozCCApNg5QGbCf8BAxRT
7w/Tqqf6cY9773z/v3S8da92v3fP74yS1H3S4AIAg6AAAAADABAQAAAAAAMAERAAAAAAAwCAEP//
//9AAAcwYEV9x9KdvAFAAAgwYEV9x9KdvAELAACACCAGAAAAAADAAAAAAAAARgAAAAADhQAAAAAA
AAMAAoAIIAYAAAAAAMAAAAAAAABGAAAAABCFAAAAAAAAAwAFgAggBgAAAAAAwAAAAAAAAEYAAAAA
UoUAALcNAAAeACWACCAGAAAAAADAAAAAAAAARgAAAABUhQAAAQAAAAQAAAA4LjAAAwAmgAggBgAA
AAAAwAAAAAAAAEYAAAAAAYUAAAAAAAALAC+ACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMA
MIAIIAYAAAAAAMAAAAAAAABGAAAAABGFAAAAAAAAAwAygAggBgAAAAAAwAAAAAAAAEYAAAAAGIUA
AAAAAAAeAEGACCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgBCgAggBgAAAAAA
wAAAAAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4AQ4AIIAYAAAAAAMAAAAAAAABGAAAAADiFAAAB
AAAAAQAAAAAAAAAeAD0AAQAAAAUAAABSRTogAAAAAAMADTT9NwAA4D4=
------ =_NextPart_000_01BC9DB1.5F9E18A0--