February 5, 2003 at 7:36 am
I am writing a delete trigger to audit the deletion made on the table from which machine. I need to log all the fields from that table to a new table, plus machinename and actiondatetime.
Error occurs: select * into #tmp from deleted
The deleted table has 3 image type columns which are not allowed in copying.
The table has over 60 columns and I don't want to list all columns except 3 image columns (3 columns never been used and cannot drop them because the table is for replication). And dynamic sql is not working for deleted table.
I need to find a way to do copying without image columns and it can be used on auditing other tables.
February 10, 2003 at 8:00 am
This was removed by the editor as SPAM
February 11, 2003 at 2:42 am
Do not know much about image columns (do not use them myself) but I believe the only way to read and write image columns is to use READTEXT and WRITETEXT.
In your case if the 3 image columns are not being used then I would list the other columns in the select statement however painful that may be.
ps you could use any scripts (or generate one) to speed up the sql writing.
Far away is close at hand in the images of elsewhere.
Anon.
February 11, 2003 at 7:28 am
Have you tried to select columns you need instead of everything (*).
Information below may help you too. That is from BOL.
"Using text, ntext, and image Data in INSTEAD OF Triggers
Data modifications may involve text, ntext, and image columns. In base tables, the value stored in a text, ntext, or image column is a text pointer pointing to the pages holding the data. For more information, see text, ntext, and image Data.
Although AFTER triggers do not support text, ntext, or image data in the inserted and deleted tables, INSTEAD OF triggers do support them. text, ntext, and image data is stored in the inserted and deleted tables differently from the way the data is stored in base tables. text, ntext, and image data is not stored as a separate chain of pages. Instead, they are stored as a continuous string within each row, which means there are no text pointers for text, ntext, or image columns in the inserted and deleted tables. The TEXTPTR and TEXTVALID functions and the READTEXT, UPDATETEXT, and WRITETEXT statements are not valid against text, ntext, or image columns from the inserted or deleted tables. All other uses of text, ntext, or image columns are supported, such as referring to them in select lists, WHERE clause search conditions, or the SUBSTRING, PATINDEX, or CHARINDEX functions. Operations on text, ntext, or image data in the INSTEAD OF triggers are affected by the current SET TEXTSIZE option, which can be determined with the @@TEXTSIZE function.
The type of text, ntext, or image data stored in the inserted and deleted tables varies depending on the triggering action (INSERT, UPDATE, or DELETE):
On INSERT statements, the inserted table contains the new value for the text, ntext, or image column. The deleted table has no rows.
On DELETE statements, the inserted table has no rows and the deleted table rows contain the values the text, ntext, or image column had before the DELETE started.
On UPDATE statements in which the text, ntext, or image value is not changed, both the inserted and deleted table rows contain the same values for the text, ntext, or image column.
On UPDATE statements in which the text, ntext, or image value is changed, the deleted table contains the data values as they existed before the UPDATE started, and the inserted table contains the data with any modifications specified in the SET clause.
If an INSERT, UPDATE, or DELETE statement modifies many rows with large text, ntext, or image values, considerable memory can be required to hold the copies of the text, ntext, or image data in the inserted and deleted tables. Copying these large amounts of data can also lower performance. INSERT, UPDATE, and DELETE statements that reference views or tables that have INSTEAD OF triggers should modify one row at a time, or only a few rows at a time, whenever possible."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply