readinf blob data in sql trigger

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
narayanan@.gsdindia.comThe text, ntext, and image values in the inserted and deleted tables cannot
be accessed within a trigger. This is by design.
Anith|||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/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sathya" wrote:

> 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
> narayanan@.gsdindia.com
>|||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|||Well, not quite. You can access them in an INSTEAD OF trigger with the
compatibility level set to 80 (or higher - someday).
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
narayanan@.gsdindia.com|||hi,
please tell me how you can do this...
sathya narayanan|||>> i have to write delete trigger which can get image data and userid in to
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.
Anith|||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.
MLsql

Re sort

Hi all
I Have Table Num As Int , Name As NvarChar(20) I Need Trigger to Resort The
Field Num When I Change The num
Num Name
1 aaaaa
2 bbbbb
3 cccccc
4 ddddd
I Want when I Change the Num 1 To 2 Came Like
Num Name
1 bbbbb
2 aaaaaa
3 cccccc
4 ddddd
Re sort The Num In Trigger
Thankswhat are you talking about? Sorting is done at query time, by specifying a
sort order.
"Taha" <taha105@.hotmail.com> wrote in message
news:%23%23nlsb9jGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi all
>
> I Have Table Num As Int , Name As NvarChar(20) I Need Trigger to Resort
> The Field Num When I Change The num
>
> Num Name
> 1 aaaaa
> 2 bbbbb
> 3 cccccc
> 4 ddddd
>
> I Want when I Change the Num 1 To 2 Came Like
>
> Num Name
> 1 bbbbb
> 2 aaaaaa
> 3 cccccc
> 4 ddddd
>
> Re sort The Num In Trigger
>
> Thanks
>|||Let's make this problem more concrete. You want to put automobiles
into numbered parking spaces and move them around.
CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(170) NOT NULL);
Re-arrange the display order based on the parking_space column:
CREATE PROCEDURE SwapVehicles (@.old_parking_space INTEGER,
@.new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @.old_parking_space
THEN @.new_parking_space
ELSE parking_space + SIGN(@.old_parking_space - @.new_pos)
END
WHERE parking_space BETWEEN @.old_parking_space AND @.new_parking_space
OR parking_space BETWEEN @.new_parking_space AND @.old_parking_space;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);|||Thank you CELKO
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150310619.762497.25420@.p79g2000cwp.googlegroups.com...
> Let's make this problem more concrete. You want to put automobiles
> into numbered parking spaces and move them around.
> CREATE TABLE Motorpool
> (parking_space INTEGER NOT NULL PRIMARY KEY
> CHECK (parking_space > 0),
> vin CHAR(170) NOT NULL);
> Re-arrange the display order based on the parking_space column:
> CREATE PROCEDURE SwapVehicles (@.old_parking_space INTEGER,
> @.new_parking_space INTEGER)
> AS
> UPDATE Motorpool
> SET parking_space
> = CASE parking_space
> WHEN @.old_parking_space
> THEN @.new_parking_space
> ELSE parking_space + SIGN(@.old_parking_space - @.new_pos)
> END
> WHERE parking_space BETWEEN @.old_parking_space AND @.new_parking_space
> OR parking_space BETWEEN @.new_parking_space AND @.old_parking_space;
> When you want to drop a few rows, remember to close the gaps with this:
> CREATE PROCEDURE CloseMotorpoolGaps()
> AS
> UPDATE Motorpool
> SET parking_space
> = (SELECT COUNT (M1.parking_space)
> FROM Motorpool AS M1
> WHERE M1.parking_space <= Motorpool.parking_space);
>

RDC user info

i want to write user info to a table when row deleted on
delete trigger.how can i get user info ?this user is using
query analyzer and connect server via remote desktop
connection. how can i get this user machine name or ip,
thanksThere are system functions that will return that info. Be aware that the
logged in application can optionally set the host information in its
connection string. SQL just reads what the lower levels tell it.
If you want the machine info, use host_name or host_id.
If you want the logged in user info, use system_user or current_user.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

RDA Push to a table that has an INSERT Trigger

Hello,

I posted under the Smart Devices VB and C# Projects forum as well, but since my question relates to SQL Server Triggers, I thought I might get some help here too. I apologize if this is not the correct forum.

I am using SQL Server 2005 Standard.

On my PC, on the table that I Push to from the device, I have a Trigger that sends an e-mail upon INSERT.

This trigger works (sends e-mail) when I run an INSERT against the table within management studio, or when I run an INSERT against the table from a web app on the same PC. (I also tested using an UPDATE, and the trigger worked.)

Here is my problem:

The trigger does not kick off after I successfully Push records from the device to the table on my PC.

Here is my c# Push code on the device:

rda.Push("tablename", rdaOleDbConnectString, RdaBatchOption.BatchingOn);

NOTE: I am not having a problem with the actual Push itself. That is working just fine.

On the surface it seems like an INSERT is happening (a new row appears in the table with the trigger), but does the Push do some other kind of action other than a true INSERT?

I can't seem to find anything about what exactly the Push is doing behind the scenes.

Can triggers be set for anything other than UPDATE, INSERT, DELETE?

Any insight would be appreciated.

Thank you.

Patrick

According to this article: http://msdn2.microsoft.com/en-us/library/aa225639(SQL.80).aspx it should work, but you might have to use SET NOCOUNT ON in your trigger (if you already haven't)

If that doesn't work, try using profiler and see what commands are actually being sent.

|||

I realized that the Trigger on the table was causing problems. I decided to remove the trigger on the table I Push into. I then wrote a stored proc to INSERT INTO another table that contains a trigger. This trigger successfully sends the single e-mail.

Now, I want to send multiple e-mails. I'll enter another post.