Trigger question for columns with text in MS sql server 2000

All,

I am adding audit trail to the database using sql server 2000.
I am being forced to use Instead of Insert trigger for tables that have text columns as sql server 2000 does not support for/after triggers for text,ntext and image columns in the table.

I want to insert the new row on the same table and then update the audit table with those values. It is just updating the audit table and the column containing the base table primary key is having the value 0. Also, It does not insert the row into the base table.
But it errors out with the message "Instead of trigger does not support direct recursion".

Example -- psuedocode
Table A (column1 int Identity(1,1) (primary key with identity), column2 varchar(10), column3 text)
Table Aaudit (to store the audit trail)
Create trigger trinsTable
instead of insert on Table A
as
begin transaction
Select into #inserted from inserted
Exec (Alter table #inserted add auditdate datetime, add audituser varchar(10))
Exec (insert TableA (column 2, column 3) Select column 2, column 3 from #inserted)
Exec (insert Table Aaudit select * from inserted)
.....
end

What is the workaround?

Also, I am considering only single row insert at the present time. There may be other tables that may have multiple row inserts. Do I have to use cursors or anything else for that?

I also tried using the for/after trigger with JOIN on the base table with the text column and that did not work.

I am running out of options and I am willing to try any suggestions!

Thanks in advance for your time

Rob

Heh... that's why I never use Text, NText, or Image in SQL Server 2000...

Just a couple of suggestions that don't really fix your exact problem... sorry for the deviation...

My question would be, do you really need a text column? Do you have any text that is over 8000 characters? If not, convert the column to VARCHAR(8000) and store it in a sister table along with the primary key of the older sister.

Even if the text is over 8000, it could be "sliced" into multiple 8000 character slices and stored in a sister table, as well.

Another alternative is to store just a file name instead of text. The file name would point to the file containing the text.

Other than that, I appologize for being of no basic use to solve this problem... I just don't use the "forbidden 3" in my databases.

--Jeff Moden