How to Copy textfields in a Trigger

[warkre]

I have written a trigger that saves a record in an audit file when the record is deleted. I want to save *ALL* records, but I cannot find how to save type [TEXT].
I get the error: "Text-, ntext- of image-kolommen kunt u niet gebruiken in de ingevoegde of verwijderde tabellen." in english: You cannot use text-, ntext or image-columns in inserted or removed tables.
But I would like to save the contents.
I cannot find anything that helps me solving this.
Can someone shed some light on this, as I am only sql-ing for some 4 weeks (and learning a lot by making mistakes)
The line commented out is a [text] field:

USE [almanak]
GO
/****** Object:  Trigger [dbo].[Audit_delete_klanten]    Script Date: 07/18/2007 14:39:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER TRIGGER [dbo].[Audit_delete_klanten] 
   ON  [almanak].[dbo].[almanak_klanten] 
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	-- Audit OLD record.
      INSERT INTO [almanak].[dbo].[almanak_klanten_audit]
			(kl_id,
			kl_zoeknaam,
.....a lot more here
--			kl_wachtl_reden_af,
			kl_datum_eindrelatie,
			kl_gearchiveerd ) 
         SELECT 
			del.kl_id,
..... the same here
--			del.kl_wachtl_reden_af,
			del.kl_datum_eindrelatie,
			del.kl_gearchiveerd
         FROM deleted del
END

 

[onpnt]

What version of SQL Server is this on?

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[SQLSister]

AS you found deleted doesn't store text values. YOu will need an instead of trigger. Then you take the identifiers in deleted and join to the main table and copy the record to the audit table. Then you perform the delete in the trigger.

"NOTHING is more important in a database than integrity." ESquared

 

[warkre]

to onpnt: SQL Server 9.0.2047 (Express version)
to SQLSister: (newbie question)is the delete buffer already filled before you delete? You say use instead of trigger.

 

[SQLSister]

yes the deleted table will be filled before the delete is committed. Instead of trigger will allow you to get what you want.

"NOTHING is more important in a database than integrity." ESquared