October 24, 2007 at 8:23 am
We've purchased a package that adds records where one of the columns is a TEXT datatype. There's a lot of information in the TEXT column we'd like to parse out and report on but so far the vendor has not agreed to do it so we thought we'd do it ourselves. The thought was to do it with an AFTER trigger but you can probably anticipate the problems we're having. TEXT columns are not available in the inserted table. I can't switch to an INSTEAD OF trigger because their code does the insert and I have no idea of everything they do. I've tried joining the inserted table against the main table but I still get null in the TEXT column.
Any other ideas?
October 24, 2007 at 9:34 am
Join your inserted table back to the actual table.
SELECT * FROM Inserted I
INNER JOIN MyTable M ON I.MyID = M.MyID
The text operations will happen before your trigger fires so you should see the text field in the actual table.
Be careful doing this with a "regular" trigger. If something happens in your trigger, you will roll back the priginal transaction and may cause some hefty errors in your third party application.
As an alternative, you could create a simple trigger that sends a message into a service broker queue and then use the service broker to process your records. This is sometimes referred to as an Asynchronous trigger. It would divorce your processing transaction from the insert transaction and yet allow you to process the data real-time.
October 24, 2007 at 9:39 am
does it HAVE to be done instantly in a trigger?
how about a scheduled job that runs periodically(or is queued based on the trigger you are building)
in that case, you could select the id,convert(varchar(max),textfield ) from sometable, and then parse the fields as needed.
would something like that be possible? a simple WHERE statement where you check whether you've already parsed a text column (other table i assume?) to keep the number of rows processed low, as well as avoiding reproccessing already parsed data.
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply