Executing APG Scripts in SQL Server Triggers

Considerations

Using the APGen COM object, a system can easily be set up so that APG scripts are executed when SQL Server triggers fire. This ensures that generated static and two-phase files always contain current data. Using SQL triggers for page updates implements a "push" model for updating page content. This provides superior performance and freshness to "pull" models, which either cache page content and poll the database for changes, or repeatedly render page content for every request.

The SQL Trigger example shows how to implement automatic page updates when a SQL trigger fires.

The primary concern when running APG scripts in SQL triggers is locking conflicts: While a SQL trigger is executing, all the changing rows are normally locked. APG scripts run in the trigger will normally run a query that returns the changing rows - but the changing rows are locked, so the query times out. The workaround is to use the WITH (NOLOCK) hint in the APG script's SELECT statement. Another workaround is to use MSMQ or queued components to execute the APG script asynchronously.

Secondary concerns are that this example runs the APG script in-process and synchronously. For high-throughput or highly-volatile databases, it may be desirable to move APG script execution to another process or another server. Also, locking the changed rows for more than the shortest period of time may be undesirable for high-traffic tables. To address these concerns, asynchronous page updates using MSMQ or queued components can be used.

It may at first concern you that this technique adds workload to the database server, and keeps rows locked longer while the trigger executes. Keep in mind that using this SQL trigger system with APG scripts will dramatically reduce the number of database reads compared to standard ASP web pages - most web page views will no longer hit the database. So a system that would be considered "high-throughput" before may be fairly low throughput after APGen optimization.

File Permissions

File permissions should be considered when designing a SQL trigger solution. The SQL Server process is normally run in the local SYSTEM account, which by default has write permissions in local directories. The considerations discussed in the Security topic with respect to service processes apply to SQL Server.

An explicit security system can be implemented: File permissions can be granted to a user account as needed. The APGen Logon objects can be used to create files using that user account. And/or a COM+/MTS out-of-process wrapper component can specify the user account to run the APG script.

Alternative Techniques

Though the "Page update in SQL Trigger" architecture has many benefits, alternative or related techniques work well in similar situations:

Code that updates the database can subsequently re-generate the changed pages. For example, an ASP page that updates the database can then execute the appropriate APG scripts to re-generated the changed pages.
The MSMQ Example addresses the locking and performance concerns discussed above, while still providing a method of updating web pages when SQL triggers fire. The MSMQ example demonstrates an asynchronous and distributed content rendering system for a large-scale site using SQL Server, MSMQ, and Active Page Generator. This system allows content to be generated on a separate APGen server, if this level of isolation is desired. Content update messages are sent from ASP pages and from SQL triggers, and are handled in a separate process. Using this architecture, the NOLOCK hint is unnecessary, and content is updated in its own process and asynchronously.

Example

This example shows T-SQL code for calling the APGen COM object in a SQL trigger to update product pages. This code is taken from the SQL Trigger example, which uses the Northwind example database (normally installed with SQL Server). Running the install.sql file in the SQL Trigger example will install the stored procedures and the trigger shown here.

Two T-SQL stored procedures provide error handling in case an error occurs while running the APG script. We recommend doing most error handling in the APG script, since APG script error handling provides more flexibility and support for writing errors to the Event log.

-- sp_hexadecimal converts a binary value to a hex string

CREATE PROCEDURE dbo.sp_hexadecimal
 @binvalue varbinary(255),
 @hexvalue varchar(255) OUTPUT
AS
 DECLARE @charvalue varchar(255)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)

 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH(@binvalue)
 SELECT @hexstring = '0123456789abcdef'

 WHILE (@i <= @length)
 BEGIN
 DECLARE @tempint int
 DECLARE @firstint int
 DECLARE @secondint int

 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 SELECT @firstint = FLOOR(@tempint/16)
 SELECT @secondint = @tempint - (@firstint*16)
 SELECT @charvalue = @charvalue +

 SUBSTRING(@hexstring, @firstint+1, 1) +
 SUBSTRING(@hexstring, @secondint+1, 1)
 SELECT @i = @i + 1
 END

 SELECT @hexvalue = @charvalue

-- sp_displayoaerrorinfo displays COM error information for an object

CREATE PROCEDURE dbo.sp_displayoaerrorinfo
 @object int,
 @hresult int
AS
 DECLARE @output varchar(255)
 DECLARE @hrhex char(10)
 DECLARE @hr int
 DECLARE @source varchar(255)
 DECLARE @description varchar(255)

 PRINT 'OLE Automation Error Information'

 EXEC sp_hexadecimal @hresult, @hrhex OUT

 SELECT @output = ' HRESULT: ' + @hrhex
 PRINT @output

 EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
 IF @hr = 0
 BEGIN
 SELECT @output = ' Source: ' + @source
 PRINT @output
 SELECT @output = ' Description: ' + @description
 PRINT @output
 END
 ELSE
 BEGIN
 PRINT " sp_OAGetErrorInfo failed."
 RETURN
 END

The sp_RunAPGScript stored procedure runs an APG script, and passes a single string argument to the APG script using APGScript.RunArgs():

-- sp_RunAPGScript executes an APG script, passing in a single string argument
-- @script: the path of the APG script
-- @arg: the argument to pass into the APG script

CREATE PROCEDURE dbo.sp_RunAPGScript
 @script varchar(100),
 @arg varchar(60)
AS
 DECLARE @oAPGen int
 DECLARE @hr int
 DECLARE @false bit

 -- Create the APGen object
 EXEC @hr = sp_OACreate 'APGen', @oAPGen OUT
 IF @hr <> 0
 BEGIN
 EXEC sp_displayoaerrorinfo @oAPGen, @hr
 RETURN
 END

 -- It's a good idea to turn off APGen debugging for production sites
 -- For dev purposes, this code can be removed to debug the APG script
 SELECT @false = 0
 EXEC @hr = sp_OASetProperty @oAPGen, 'Debug', @false

 -- Call APGen.RunArgs( @arg )
 EXEC @hr = sp_OAMethod @oAPGen, 'RunArgs', NULL, @script, @arg
 IF @hr <> 0
 BEGIN
 EXEC sp_displayoaerrorinfo @oAPGen, @hr
 RETURN
 END

 -- Destroy the object.
 EXEC @hr = sp_OADestroy @oAPGen

Next, an INSERT and UPDATE trigger is added to the Products table, which runs prod.apg once for each row that is added or updated:

CREATE TRIGGER dbo.tr_Products_Update ON dbo.Products
FOR INSERT, UPDATE
AS
     DECLARE @sID VARCHAR(20)
     IF( @@ROWCOUNT = 1 )
     BEGIN
          -- A single row was inserted or updated
          SELECT @sID = LTRIM(STR(ProductID)) FROM inserted
          EXEC sp_RunAPGScript "C:\Program Files\APGen\Examples\SQLTrig\prod.apg", @sID
     END
     ELSE
     BEGIN
          -- Multiple rows were inserted or updated
          -- Need to run the APG script once for each row
          DECLARE Products_Cursor CURSOR FAST_FORWARD FOR SELECT CONVERT( VARCHAR(50), ProductID ) FROM inserted
          OPEN Products_Cursor
          FETCH NEXT FROM Products_Cursor INTO @sID
          WHILE @@FETCH_STATUS = 0
          BEGIN
               EXEC sp_RunAPGScript "C:\Program Files\APGen\Examples\SQLTrig\prod.apg", @sID
               FETCH NEXT FROM Products_Cursor INTO @sID
          END
          CLOSE Products_Cursor
          DEALLOCATE Products_Cursor
     END

The APG script prod.apg generates a single static web page per product. Note that the WITH (NOLOCK) hint is used in the SQL query - this is necessary for avoiding a deadlock.

<%# @LANGUAGE="VBScript" #%>
<%# Option Explicit #%>

<%#

' Validate args
' Arg(0) should be a product ID
Dim sID
sID = Script.Arguments(0)
If (IsEmpty(sID)) Then
 Stop ' Runs debugger if debugging is enabled
 Script.Abort
End If

' Query for the product from the DB
' The WITH (NOLOCK) hint is very important!
' Failure to use this hint while running within a trigger
' can result in a deadlock.
Dim sSQL, rs
sSQL = "SELECT C.CategoryName, C.Description AS CatDescription, P.ProductName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock FROM Products AS P WITH (NOLOCK) INNER JOIN Categories AS C ON P.CategoryID=C.CategoryID " & _
 "WHERE P.ProductID=" & sID
Set rs = OpenRS(sSQL)

' If rs is empty, abort
If rs.EOF Then
 Stop ' Runs debugger if debugging is enabled
 Script.Abort
End If

' Set filename
Output.Dir = Script.Dir & "out" ' subdir of APG script
Output.Filename = "prod_" & sID & ".htm"
Output.CanCreateDirs = true

' Output.Unicode can optionally be turned on - just uncomment this next line
'Output.Unicode = true

#%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv="Content-Type" content="text/html; charset=utf-7" >
<TITLE>Northwind: <%#= rs(2) #%></TITLE>
</HEAD>
<BODY bgcolor=Burlywood>


<TABLE width=400 border=2 cellpadding=2 cellspacing=0 bordercolor=DarkRed>
<COLGROUP><COL valign=top width=100><COL valign=top width=300></COLGROUP>
<TR>
 <TD colSpan=2 bgcolor=DarkRed>
 <%#= rs(2) #%>
 </TD>
</TR>
<TR>
 <TD>Category:</TD>
 <TD><%#= rs(0) #%></TD>
</TR>
<TR>
 <TD>Category Description:</TD>
 <TD><%#= rs(1) #%></TD>
</TR>
<TR>
 <TD>Unit:</TD>
 <TD><%#= rs(3) #%></TD>
</TR>
<TR>
 <TD>Unit Price:</TD>
 <TD><%#= FormatCurrency(rs(4)) #%></TD>
</TR>
<TR>
 <TD>Units In Stock:</TD>
 <TD><%#= rs(5) #%></TD>
</TR>
</TABLE>


</BODY></HTML>
<%#

rs.Close
Set rs = Nothing

#%>