|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
August 20th, 2003, 11:47 PM
|
|||
|
|||
How to make a trigger generate an html page?
I am using MS SQL 7 and wish to create an html page when a table is updated. Can a trigger create an html page based on a query? Can a trigger call a VB.NET function or something that will allow me to query the modified table, manipulate the data and save it to an html file?
Thanks, Tim_Geiger@hotmail.com
|
|
#2
August 21st, 2003, 01:08 AM
|
||||
|
||||
|
Why would you want to create a static file when table is updated? I don't follow. I would've thought the normal path to take would be to write a ASP (or ASP.NET) file that performs your queries.
e.g. Have a page that displays all your records, or just a persons name or something... then you click their name and bring up their profile... etc...
|
|
#3
August 21st, 2003, 11:08 AM
|
|||
|
|||
|
Well, we are using .asp. And every time the .asp page is pulled up by a web client we could query the database, turn the resulting record set into an array, and then turn the appropriate values in the array into an html pulldown menu and then just display the .asp page.
But it occurs to us that several of these pulldown menus will contain data that changes very infrequently. An example of this might be a pulldown menu of CPU processor manufacturers. Intel, Motorola, Sun, AMD, etc. We may need to add Texas Instruments sometime in the future, but its not a table that will change often, if at all. So the question is - why should the .asp page query the database and build a menu on the fly everytime the page is looked at, when the data hardly ever updates? To me it seems like its a lot of wasted processing power. We imagine this would be especially problematic when there are 30 or so of these pulldown menus on one page. Server side includes in an .asp page are extremely cheap in terms of processing power, time, etc. Wouldn't it be better to for the .asp page to include a flat .html file with the appropriate data in the pulldown menu? There is no processing power involved at all. It would be quick and easy. If and when the CPU manufacturer table is updated in SQL, a trigger could fire off a query that would write over the included .html file with the new data. I think it would be pretty speedy, which is what we are looking for because of the nature of the page, and the age of ther server (a dual PII Dell). What do you think?
|
|
#4
August 21st, 2003, 02:33 PM
|
|||
|
|||
|
It seams to me like a complicated method, but creating the file is possible:
http://www.w3schools.com/asp/asp_ref_filesystem.asp
|
|
#5
August 21st, 2003, 04:59 PM
|
|||
|
|||
|
Except he wants to do it from within SQL Server, not from within ASP, so your best bet is to look up "BCP" in Books Online.
|
|
#6
August 21st, 2003, 06:58 PM
|
||||
|
||||
|
If you have data that changes infrequently, you can cache it in the "Application" session. I use this technique quite a bit for holding my site template files (static html strings), as they do not change often.
It's very simple to do. Code:
'--- check if your data is cached
if Application("theCachedArray") = "" then
'--- here, get your recordset, convert it to an array
'--- insert the array into the application session
Application("theCachedArray") = aryData
end if
'--- pass the cached data out to a variable to be used as normal
aryData = Application("theCachedArray")
When caching data, you then get the speed benefits, as the data in stored in memory. Heres where i learnt the technique: http://www.4guysfromrolla.com/webtech/052099-1.shtml. Another note - in proper 3-tier programming, databases should be used for data storage only. Although what you're attempting to do isn't really violating that law (tho you are now loosing contorl over your presentation layer, as it is locked away on the DB - not ideal), I just thought I'd mention it, as I've noticed a lot of people trying to cram their business logic into triggers lately.
|
|
#7
August 22nd, 2003, 01:22 PM
|
|||
|
|||
Well, it is a little more complicated to program initially I suppose. But I believe it will end up with a better product. The hardware we have to run this on is a little older. Also the page in question HAS to be as fast as possible and will contain 38 pulldown menu's that don't change much - but do change. I am afraid that generating all these on the fly would slow the page too much.
The problem with an application variable is getting them to update when the table updates. The tables may be updated out side of the web application. Interestingly, numbernine's suggestion to use BCP is quite simple and does what I want quite well. There is a great example of this here: http://www.sqlteam.com/item.asp?ItemID=4722 Writing my own Stored Proceedures to create the text files I needed was easy. Thanks numbernine. I have taken your suggestion Brother, and I have used it well.
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > How to make a trigger generate an html page? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
