hi,

i am using sql sever 2005 , i want to read binary data and data of
computed coloumn using trigger.

my table is below:

table column
------------
1)  document data -- type (image)
2)  userId ---------  type(varchar computed column)


i have to write delete trigger which can get image data and userid in
to some variable or cursor.

please can anyone help me in this regard.


sathya narayanan.v
naraya***@gsdindia.com

The text, ntext, and image values in the inserted and deleted tables cannot
be accessed within a trigger. This is by design.

Well, not quite. You can access them in an INSTEAD OF trigger with the
compatibility level set to 80 (or higher - someday).


ML

hi,
please tell me how you can do this...

sathya narayanan

You will have to elaborate on what you are trying to do. As I said, you
cannot use image data within a trigger. You cannot declare a variable of
image type either.

If you are stuck with this table, you might have to use a different
approach, perhaps using a stored procedure or temp table with column of
image type etc.

Hi

If you are using SQL Server 2005, replace the image datatype with
varbinary(max) as the image datatype will be removed in future releases of
SQL Server.

Post SQL Server 2005 CTP/Beta questions to:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005
Show quoteHide quote

Do you want to manipulate this data in the trigger or would you like the
trigger to return it? Since the latter is impossible, please elaborate on
what you actually want to do.

The sole purpose of the image datataype is to enable storage of large binary
data in SQL. Manipulating it is IMHO a matter for the application layer,
rather than the data/storage layer. But please, prove me wrong.


ML

hi,

thank you for reply..
actually i want to create trigger on delete event which gets image and
computed coloumn data from one table to another table. Is it possible
to get image data (blob ) and computed data to  read from sql in delete
trigger event.
i have posted my databse coloumns below:

Id Uniqueidentifier
SiteId Uniqueidentifier
OldDirName  varchar(256)
OldLeafName  varchar(128)
WebId Uniqueidentifier
DeletedListId Uniqueidentifier
DeletedDocLibRowId int
Type tinyint
Size int
MetaInfoSize int
Version int
UIVersion int
Dirty bit
CacheParseId Uniqueidentifier
DocFlags tinyint
ThicketFlag bit
CharSet int
TimeCreated datetime
TimeLastModified datetime
NextToLastTimeModified datetime
MetaInfoTimeLastModified datetime
TimeLastWritten datetime
SetupPath nvarchar(255)
CheckoutUserId int
CheckoutDate datetime
CheckoutExpires datetime
CheckoutSize int
VersionCreatedSinceSTCheckout bit
LTCheckoutUserId int
VirusVendorID int
VirusStatus int
VirusInfo nvarchar(255)
MetaInfo binary(8000)
Content binary(8000)
CheckoutContent binary(8000)
Extension  varchar(128)


I have to read all coloumn values when in trigger for deleted row and
keep data in another coloumn.
I faced problem while reading image and computed column data.

please help me.
sathya narayanan v
naraya***@gsdindia.com

You should be using something like this in your trigger:

insert destination_table
  (
  ...
  )
  select ...
           from inserted

for the insert/update trigger
and

insert destination_table
  (
  ...
  )
  select ...
           from deleted

for the delete trigger.

You cannot store image values in triggers, since text, ntext and image
datatypes cannot be used for variables.

And if you had used them, such a trigger wouldn't have supported multiple
inserts/updates/deletes.


ML

INSTEAD OF INSERT TRIGGER AND NULL
obtaining the ip address of connection
Use a stored procedure from a function
triggers
User Defined Function