Trigger failing with 7113 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger failing with 7113

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-06 : 08:32:15

Shamsu writes "I have a very simple trigger that saves a deleted record into another table for archiving. I receive errors on certain delete operations especially pertaining to text field data. I get the following error- Data size mismatch detected when transferring TEXT/IMAGE value
Server: Msg 21, Level 22, State 1, Procedure
trg_inbox_bess506a_mstr_on_del,
Line 8
WARNING - Fatal Error 7113 occurred at Dec 22 2004 11:25PM. Please
note the
error and time, and contact your System Administrator.

I am inserting into a text field with 1886 chars, that's the one that fails. Is there a work around or solution. I am working in sql server 6.5., old story.

Here is the trigger:
"Create Trigger trg_inbox_bess506a_mstr_on_del
On dbo.inbox_bess506a_mstr
For Delete
As
-- 040226, archive inbox to arc
set nocount on
insert into inbox_bess_mstr_arc (
pk_id,
batch_id,
py,
appropriation,
issueFrom,
issueTo,
submitBy,
submitDate,
validID,
validDate,
approveDate,
approveBy,
accountCode,
transType
--remark
)
select
pk_id,
batch_id,
py,
appropriation,
issueFrom,
issueTo,
submitBy,
submitDate,
validID,
validDate,
approveDate,
approveBy,
accountCode,
transType
--remark
from deleted
return

GO""

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-06 : 08:59:49

from Books OnLine:

quote:

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources