inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Well, what I was really wanting to know is if I could assume that the data in row [1] (the new data to be inserted) of the inserted table corresponds with the data in row [1] (the data that was deleted) in the deleted table.

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

This type of update statement will only work if there is a single row being updated. I wanted to step through the inserted and deleted tables one row at a time for multiple row updates, but I did not know if it was safe to say that the data in inserted row # corresponded with the data in deleted row #.

|||

> Update people

>

> Set person_id = (select person_id from inserted)

>

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

It is possible to update the unique key for multiple rows in a table. In that case, there is nothing to correlate the rows in "inserted" to the rows in "deleted" other than the order in which they are returned by a select statement.

So the question is a valid one, I think: If a table contains one unique key, and multiple rows in that table are updated such that the value of that key changes, can we count on the rows in the "inserted" and "deleted" tables being returned in the same order so that they can be matched up one to one?

Thanks,

Ron

Inserted/deleted table.

Hi,

I am currently working on a MS SQL server 2000.

I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted -- are not accessible. anyone knows why? And is there any other way to do this?

Thankspost your t-sql code that you used to access the inserted/deleted tablessql

inserted value on text field gets truncated after 255 chars

Hello,
I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
statement over a table. In the table I have two 'text' fields with the
same properties, with just one difference: one field allows nulls, the
other one does not.
Well, one field actually accepts only the first 255 chars (the nullable
field), while the other field has no problems.
The "Length" property is set to 16 for both fields, as I said all the
properties but one (null/not null) are exactly the same, and also the
context is the same (same database, same table).
Many thanks for your help!
GiovanniHow does your SP look?
It sounds like you truncate it somewhere there. Maybe the parameter is
a varchar or something like that?|||How are you validating that only 255 characters are there? Are you using
SELECT DATALENGTH(col_name) FROM table? Or are you counting the number of
characters in the result set?
"gm1974" <gmascia@.gmail.com> wrote in message
news:1138737544.776002.219580@.f14g2000cwb.googlegroups.com...
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the nullable
> field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
> Many thanks for your help!
> Giovanni
>|||gm1974 wrote:
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the
> nullable field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
>
Are you testing it in QA? If so, you should modify the "maximum characters
per column" setting in the QA options dialog.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Oh, I must be really tired. I definitely forgot to change parameter
type in the SP, it was still set at VarChar(255), so the value was
truncated!
Better to get some sleep, many thanks for your help.|||Thanks for your help, it may be useful in the future.
Giovanni

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satish
Hi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default...b;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command

create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')

when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067

how i can i view the image?

pls clarify my doubt

satishUse WRITETEXT/READTEXT instead of INSERT/SELECT to store / retrieve
There is quite a detailed explanation in Books Online

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141732028.000941.183610@.e56g2000cwe.googlegr oups.com...
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
>
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
>
> pls clarify my doubt
>
> satish|||satish (satishkumar.gourabathina@.gmail.com) writes:
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?

You have not inserted the the image into the table. You have inserted the
disk location of the image into the table. Run

SELECT convert(varchar(80), d) FROM aa

to see.

There is no way to insert data into a column directly from a file. The
normal way of loading image is write a program that reads the file,
and the passes the binary stream through a parameterised INSERT statement
in a client API. You can also convert the contents to a hexstring and
build an INSERT statement from that.

Conversly, to display the image you also need an application. If you
have stored an image in a table, a SELECT on that table in Query Analyzer
or Mgmt Studio will only display a long hex string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql