(OT?) MSDE, SQL Server, Triggers and sequential transaction processing How's that for a mouthful! Parading my ignorance a little here, please hang tough ... I am parsing my weblogs into a reporting database, I started doing this because we never had a reporting tool that I liked to get the info out of the weblogs, and I needed something to do some basic reporting. I now have access to a webtrends server, pretty much the latest version, but like the idea of be able to custom process the log files. So I am still playing. The import process is pretty simple. I import the logfile to a temp table, and create a record in an importLog table storing basic info about the file that was imported and a process status field. I import all valid request records which allows me to disect the whole file into status, IP, browserAgent, page extension, this was useful when I was (still am) learning what the information in the logfile really means. I'm cheap, so I using MSDE now, and promising to scale the reporting server to SQL Server in the future (when the glorious days of "revenue" happen - the smart guy in the back of the room says - get SQL Server today!). The second step is to filter the RAW information, and I do this as a second step rather than on import, because I wanted to use a trigger to process each record and write data to several tables - a processed transaction table and several summary tables. First Question here I guess. I did it this way mainly because I am learning SQL Server in my own, and thought triggers were kinda cool cause you can do this sort of thing. So the question is, is this a reasonable approach? import to tmp, copy filtered records to an identicle table which triggers processing and summarization? I also really like the idea of being able to parse out cookie and query values as part of the processing. But my real question is. In the trigger can I create a variable on one record that stays initialized throughout the whole copy process? In other terms I mean - I want to track sessions based on IP addresses and BrowserAgents, until the member logs in, and then on a parsed out cookie value. When the session expires I want to write a session summary record(s) to session summary table(s). trigger pseudo coded. record copied process record is it a current session if yes update session information if not open new session tracker Just thinking aloud ... I have some other ideas for approaches. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
