| Author |
Topic |
|
lgarcia3
Starting Member
15 Posts |
 Posted - 2006-03-02 : 11:56:59
|
| Hi, I am working with some triggers in SQL server 2000. While doing an update, I want to copy the old record, as well as the new one to a new table so we can have a track of when the record was changed (an audit table). Now, what I do is, in the trigger, I access the tables "deleted" and "inserted". This works just fine if the table being modified does not have a field data-type "Text". In our case, the table does have a field which data type is Text. This is a well known bug in SQL Server and is corrected by installing SP3. The problem is that this is a very heavy used server and we cannot just shut it down for maintenance and the triggers are needed right away. So, is there a way around it? How can I copy those fields without having this error. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
lgarcia3
Starting Member
15 Posts |
Posted - 2006-03-02 : 15:59:11
|
| Can't do that (obmit the field... I had already thought about it! :) ) In fact that field happens to be one of the most important ones in the table |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-03 : 04:02:25
|
| "This is a well known bug in SQL Server and is corrected by installing SP3"It is??I thought the only way to process TEXT columns in [inserted and deleted pseudo tables in] a trigger was to use an INSTEAD OF trigger.At a quick glance I see nothing in SP3a, or SP3, that makes reference to triggershttp://support.microsoft.com/?kbid=306908Having said that, if this is a public-facing server I would be very nervous about running it on a pre-SP3a service pack for vulnerability reasons.Kristen |
|
|
|
lgarcia3
Starting Member
15 Posts |
Posted - 2006-03-08 : 05:39:06
|
It is Kristen. Read this:http://support.microsoft.com/kb/884855 and if you read the status part, it says:quote:
STATUSMicrosoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
Now, I installed SP4 and guess what?... it did not correct it. I guess is good old MS stuff.[url][/url] |
|
|
|
lgarcia3
Starting Member
15 Posts |
Posted - 2006-03-08 : 05:44:46
|
quote:
Originally posted by X002548
And why is it text?What does SELECT MAX(DATA_LENGTH(texCol) FROM TableGive you?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
Good question. I have no idea... If you are very interested, you may contact Made2Manage :) ... the company that created the ERP this database is for. I thought it was pretty stupid myself considering that it is just an address field. Not a memo!! So, go figure. It looks to me someone with no knowledge of this issues did that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-08 : 08:41:54
|
FWIW BoL says this which suggests to me that Instead Of triggers are required)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. If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.
So I'm not sure what the KB article refers to! Maybe its to do with triggers that do NOT attempt to reference the Text columns, but nonetheless raise an error?Kristen |
|
|
|
lgarcia3
Starting Member
15 Posts |
Posted - 2006-03-08 : 11:09:49
|
| Hi Kristen,I had read that from BOL. In fact I used it to partially solve my problem. It solved the part for update and insert; but not for delete. On delete I guess I do have to use "instead of"... it really sucks because the table in question already has several "for delete" triggers which means that I may have to re-arrange the whole enchilada. If there is a way around that you or someone else knows, please let me know. I really don't want to mess with the existing triggers |
|
|
|
|
Data type problem