var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: using 'text' type column in delete trigger from deleted table
I have a delete trigger which needs to insert data (for archiive purpose) from deleted table . here is the code :
create trigger td_member_message on member_message
declare @numrows int,
select @numrows = @@rowcount, @now = getdate()
if @numrows = 0
select d.*, @now
from deleted d
/* Errors handling */
raiserror @errno @errmsg
But I am getting error here like:
Msg 311, Level 16, State 1, Procedure td_member_message, Line 20
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I know using of instead of trigger can get rid of this error but I cannot think of using instead of trigger in this case.
CAn anyone pls help me?
Thanks in advance
Reply With Quote
Can you give the DDLs of the tables member_message and member_message_arch.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
CREATE TABLE [dbo].[MEMBER_MESSAGE] (
[message_id] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[type] [tinyint] NOT NULL ,
[subject] [varchar] (255) NULL ,
[body] [text] NULL ,
[ref] [numeric](10, 0) NULL ,
[created] [datetime2] NULL ,
[modified] [datetime2] NULL ,
constraint pk_member_message primary key clustered (member_message_id) )
Member message archive has the same structure with extra field dattime
SQL Server version please?
text was deprecated in SQL Server 2005 and the advice is to use varchar(max) or nvarchar(max) in its place
sqlserver veriosn 2008
Change your datatype to varchar(max) and your problem should go away