Does anyone know how
-- during an insert --
to automatically populate a field with the same data as the field in the
last inserted row?

Thanks.

rmg66 wrote:
First, you need a way to identify the last inserted row. is there an
InsertionDate column?

You could use a insert trigger on the table.

If value is specified add it as an extended property to the table.
If the value is not specified lookup the extended property on the table.

John

Show quoteHide quote

John Kendrick wrote:
Why are you replying to me?
I'm not the OP ;-)

Which gets us back to:
Show quoteHide quote

What does an extended property have to do with data in the table?

You could create an extended property for each columns that is use the last
inserted record.
When the insert trigger fires it will lookup and use that last column value.

The extended property is an alternative way to handle getting the last
record value without querying the table. Otherwise you would need some way
to find the latest record. Like max(datetime) as Bob mentioned.

John

Show quoteHide quote

Can you please elaborate on this approach a bit? Perhaps an example would be
helpful.

Here is an example I just tested:

CREATE TRIGGER [ABC]
ON tablename
FOR INSERT
AS

    declare @last_value AS sql_variant
    declare @count int
    declare @pk int
    declare @descrip varchar(4000)

    declare curInserted cursor local forward_only for
        SELECT [table_pk], [descrip] FROM INSERTED

    OPEN curInserted

    FETCH FROM curInserted INTO @pk, @descrip
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Check for NULL fields in INSERTED
        if @descrip is null
        begin
            -- Lookup last value

            SELECT @last_value = [value] FROM
            UPDATE [fs_actions] SET [descrip] = CAST(@last_value AS
varchar(4000)) WHERE tablename.[table_pk] = @pk
        end
        else
        begin
            -- Save last value

            select @count=count(*)
            from
            -- select appropriate stored procedure to pass parameters
            if @count=0
                -- doesn't yet exist
                exec sp_addextendedproperty 'last_value', @descrip, 'user',
dbo, 'table', 'tablename', 'column', 'descrip'
            else
                -- already exists
                exec sp_updateextendedproperty 'last_value', @descrip,
'user', dbo, 'table', 'tablename', 'column', 'descrip'
        end

        FETCH NEXT FROM curInserted INTO @pk, @descrip
    END

    CLOSE curInserted
    DEALLOCATE curInserted





Show quoteHide quote

Thanks John,

Finally someone that actually answered the question that I asked!!!

I think I can make something like this work.
Thanks for the idea.

Robert



Show quoteHide quote

Show quote Hide quote John,

That's original at least but it's also very poor solution IMHO. What
advantage does this offer over representing the data in the table? I can't
think of any reason or sense in using extended properties to store data at
runtime. Nor do I see a reason to put a cursor in a trigger.

That's the part that made me cringe the most.  And you're absolutely right,
this is far worse than storing data the way data was meant to be stored,
IMHO.

A

He's not using the property to "store"  data in the typical way.
The property is being used as a kind of place-holder so that he can access
the value and store it properly in the table as needed. Read the code.

If you guys have better idea, let's hear it!

Robert



Show quoteHide quote

See my earlier reply.

David,

I'm not saying this is the best solution, but so far I haven't seen a post
that solves the problem which I believe this does.

I don't understand your question about "representing the data in the table"
also. If you are suggesting to create a table to hold the last used value,
fine. This is just a simple solution, requiring little effort to implement
that is up for enhancement by whomever wants to use it. I'm not intended to
store data in the extended property just use them as a temporary holding
location. Also for this situation wouldn't a CURSOR be required to handle
multiple rows inserted at the same time such as in a SELECT INTO clause.

John

Show quoteHide quote

[Comments inline]

Did you see my suggestion? (BTW. Small typo. ORDER BY should be DESC)

Tables are what we use to store data in SQL. What I'm asking is why do you
feel the need to use extended properties to store data, especially given
that you are only duplicating information already in a table?

The more I look at it the less attractive it becomes. Looking at the code
for sp_updateextendedproperty it seems that this will have the effect of
serializing every insert. Now this may be what you intended and it may even
be implied by the OP's slightly ambiguous spec but I doubt it's a feasible
solution in most cases. "Simple solution" is certainly not a phrase I'd use
to describe it, given the more conventional alternatives.

No. Don't use cursors in triggers at all. Updates are set-based so triggers
should be too.

David,

I didn't see post until after I posted. Thanks Internet.

I agree with your solution far as using the a subquery to get the latest
record. The only reason I proposed to NOT do that (which I thought about)
was the OP didn't specify his table therefore I made minimal assumptions
(such as no way to guarantee the which record was inserted last).

As far as the CURSOR issue goes, I agree not to use them whenever possible.
However, I didn't see a way around it.

This is why we have newsgroups, so everyone can chime in and learn.

John

Show quoteHide quote

.... and in that case the question is unanswerable by any method.

Absolutely :-)

Thank you everyone for the input. It was fun!

Robert


Show quoteHide quote

SQL server has no concept of Last Inserted Row.
If you have a column on the table that is populated with the date and time
the row was inserted, you could query that row. But it's not impossible that
2 or more rows can be inserted at exactly the same time.
Also, in your case, what happens if the value later changes in that Last
Inserted Row.
If you tell us what the logic is behind what you're trying to do, someone
here could perhaps suggest a better solution.

Does anyone know why
-- during an insert --
you would want to do this.

I mean if you are doing a select into or and insert select then you can
choose the data you want, if you are doing simple insert stmts. then you
already know what the data is.

post some ddl and maybe we can help

Show quoteHide quote

Since a table is an unordered set of rows, how do you define "last"?

And why not keep this "last" value in another table?

This makes no sense.  An INSERT INTO statement puts an entire set of
rows into the table.  This is one of the many ways that rows are NOT
records, fields are NOT columns and tables are NOT files.

In an RDBMS, all relationships are shown by scalar values in a column.
In the DDL that you did not post, which of the columns labels the last
inserted row?  How does it handle a multi-row insertion?   I hope you
known better than to use an IDENTITY column.

Thats interesting, does the IDENTITY property not return a scalar value
then?

As usual you spout rubbish.

Show quoteHide quote

then? <<

Can you tell me the attribute measured and scale used by an IDENTITY?
Where do I find this attribute in the data model or the real world?
How do I validate and or verify it?

If I do this "measurement" repeatedly, do I get the same value (plus or
minus an error)?

If I take this "measurement" on the same data with a different machine,
do I get the same value (plus or minus an error)?

Get a copy of SQL PROGRAMMING STYLE and look at the chapters on scales
and measurements.  I always start my RDBMS classes with data and
measurement theory because it is not taught any more.

The scale and precision are whatever you want to make it in terms of the
data type you apply it to; you also set the seed - check the current ANSI
standards for more information.

Yes, you get the same value each time, even on transfering the data into
another database.

The IDENTITY property also has the benefit of preventing fraud by the read
only nature of the value - so it is truely valid, rather than portraying the
data integrity error you get when a primary key value is changed within the
database but old connections are still using the previous version - a very
dangerous situation and one that completely confuses the user.

Your books do not interest me in the slightest, they teach an old ANSI
standard at that - why do you not use the current one? Why use one that is 6
years old and out of date?

Your books serve one purpose, to show beginners just how not to do things -
your concepts are old and out-dated and belong back 15 years ago in the
mainframe world.

You should update your knowledge and get some experience of present day
concepts - directly, rather than reading a book or playing with a product.

Show quoteHide quote

Joe, that reminds me of my old question I've already asked before but
never got answered.

I have peeked inside the book, thanks to Amazon, and I'm a little bit
surprised:

The recommendation to use stored procedures (p. 122) and not to use
functions (p. 123) - is it really consistent? You list "portabilty
problems" as the reason to avoid UDFs, but for
- T-SQL (MS SQL Server)
- PL/SQL (Oracle)
- SQL-PL (DB2)
SQL UDFs are just as portatble, or not portable, as stored procedures.
What platforms are you writing about?
I don't see any major difference in portability between stored
procedures and UDFs. Could you please explain?

Also the statement on the same page that udfs are not inline seems to
be not entirely true, sometimes they are (MS SQL Server):

create table states(state char(2))
insert into states values('IL')
insert into states values('MI')
insert into states values('WI')

create table stateNames(state char(2), sname varchar(20))
insert into stateNames values('IL', 'Illinois')
insert into stateNames values('MI', 'Michigan')
insert into stateNames values('WI', 'Wisconsin')

CREATE FUNCTION getAllStateNames()
RETURNS TABLE
AS
RETURN ( select sname from stateNames )

now look at the execution plan of this statement:

select * from states,getAllStateNames()

obviously the UDF's body is compiled into it. the plan clearly
describes access to stateNames

I think the author implies language structures
similar to Oracle's CREATE JAVA statement and CREATE PROCEDURE and CREATE
FUNCTION statements with call_spec
(CREATE PROCEDURE do_something_with_java AS LANGUAGE JAVA)
rather than proprietary implementations of SQL/PSM
---
Odegov Andrey
avode***@mail.ru
(remove GOV to respond)

Show quoteHide quote

I think the author implies language structures
similar to Oracle's CREATE JAVA statement and
CREATE PROCEDURE and CREATE FUNCTION statements with call_spec
(CREATE PROCEDURE do_something_with_java AS LANGUAGE JAVA)
rather than proprietary vendor-specific implementations of SQL/PSM
---
Odegov Andrey
avode***@mail.ru
(remove GOV to respond)

Show quoteHide quote

ARRRGH! mts and its gprs service
---
Odegov Andrey
avode***@mail.ru
(remove GOV to respond)

Show quoteHide quote

- T-SQL (MS SQL Server)
- PL/SQL (Oracle)
- SQL-PL (DB2)
SQL UDFs are just as portable, or not portable, as stored procedures <<

No, the various 4GLs have very different syntaxes.  PL/SQL and SQL/PSM
are  based on Ada; Informix 4GL is in the Algol family; T-SQL is more
of C family; others are more OO; etc.

Moving 4GL code is hard, even with tools.  But a simple sequence of SQL
statements can be ported easily and can be optimized.

Look at your own example -- RETURNS TABLE is very proprietary and it
has no parameters.  It is a shell around a simple query, so it is not
really a function.  YOu are playing with syntax and not semantics.

Write a more typical function with some parameters and a return value
(compute a check digit, do some math on several columns in each row,
etc.) and you will find that you cannot use indexes and that you have
to process the rows one at a time.  But if you put the computations
into a procedure that works on entire tables, then things will usually
run faster.  You can use VALUES(), CASE expressions and auxiliary
tables to avoid looping and if-then control structures.

Even better, put the computations into CHECK() and other declarative
structures, so that you just read the data.   I have an article coming
out soon on this topic at DBAzine.com.  It is taken from a posting
where somone wrote a T-SQL check-digit function.  I then re-wrote it as
better function, a procedure and finally a CHECK() constraint.

No, the various 4GLs have very different syntaxes.  PL/SQL and SQL/PSM
are  based on Ada; Informix 4GL is in the Algol family; T-SQL is more
of C family; others are more OO; etc.


Moving 4GL code is hard, even with tools.  But a simple sequence of SQL

statements can be ported easily and can be optimized.
<<

So what? Regarding Big 3:
- T-SQL (MS SQL Server)
- PL/SQL (Oracle)
- SQL-PL (DB2)
SQL UDFs are exactly as portable as stored procedures, no more, no
less. Basically you need to rewrite everything from scratch, except for
SQL statements. The statement that stored procedures are more portable
than UDFs seems to disagree with the reality.

Look at your own example -- RETURNS TABLE is very proprietary and it
has no parameters.  It is a shell around a simple query, so it is not
really a function.  YOu are playing with syntax and not semantics.
<<

My example serves exactly one purpose: to demonstrate that UDFs can be
inline. You made a blanket statement that UDFs cannot be inline, and I
came up with the simplest possible example to PROVE otherwise.
The argument that "RETURNS TABLE is very proprietary" is very poor, as
all UDFs and SPs always start with proprietary code. This UDF is just
as proprietary as any other one. The argument that "It is a shell
around a simple query, so it is not really a function" is very poor
either, because:
1. we practitioners don't use complex queries when a simple one would
do
2. It is a function returning a result set.

(compute a check digit, do some math on several columns in each row,
etc.)
<<

what makes you think that UDFs returning result sets are not typical?

Actually no, they can not.  Who told you that?

NO!  No, not  and nuts and whipped creamed on not!  Totally different
error handling models in all of the current proprietary and the
ANSI/ISO models for it!!

Basically you need to rewrite everything from scratch, except for
ANSI/ISO  SQL statements.
Look at your own example -- RETURNS TABLE is very proprietary and it
has no parameters.  It is a shell around a simple query, so it is not
really a function.  YOu are playing with syntax and not semantics.
<<

My example serves exactly one purpose: to demonstrate that UDFs can be
inline. You made a blanket statement that UDFs cannot be inline, and I
came up with the simplest possible example to PROVE otherwise.

The argument that "RETURNS TABLE is very proprietary" is very poor, as
all UDFs and SPs always start with proprietary code. This UDF is just
as proprietary as any other one. The argument that "It is a shell round
a simple query, so it is not really a function" is very poor either,
because:

1. we practitioners don't use complex queries when a simple one would
do 2. It is a function returning a result set.

what makes you think that UDFs returning result sets are not typical?

"statements can be ported easily and can be optimized" is an excerpt
from your own post:

"Moving 4GL code is hard, even with tools.  But a simple sequence of
SQL
statements can be ported easily and can be optimized."

I never argeed to that.

Assuming you have a column / columns that enable you to define "last
inserted" and if you are happy to make the required column nullable then try
the following. I agree with others that something may not be right here. I
suspect either your logical model may be faulty or that this is something
better to do in your client / middle tier.

CREATE TABLE tbl (x INTEGER PRIMARY KEY, z INTEGER NULL, created_dt DATETIME
NOT NULL DEFAULT CURRENT_TIMESTAMP);

GO

CREATE TRIGGER trg_tbl ON tbl FOR INSERT
AS

  UPDATE tbl
   SET z =
   (SELECT TOP 1 z
    FROM tbl
    WHERE z IS NOT NULL
    ORDER BY created_dt,x)
   WHERE z IS NULL
    AND EXISTS
     (SELECT *
      FROM Inserted
      WHERE x = tbl.x) ;
GO

INSERT INTO tbl (x, z) VALUES (1,1);
INSERT INTO tbl (x) VALUES (2);

SELECT * FROM tbl ;

Thanks David,

But your solution does not address multiple inserts with various values.

for example...

if my last value was "red" and I wanted any new null values to adopt the
last value...

and I did a multiple insert such as:

Null.
Null,
"yellow",
"yellow",
Null.
Null

all four nulls would be set to "red" and the last value would also be set to
"red"
but that would be wrong, because the last two nulls should be "yellow" and
my ongoing last value should be "yellow"

Unless you have another idea, I think I might need a cursor to accomplish
this.

Robert




Show quoteHide quote

Show quote Hide quote What is it that defines the ordering of "first" and "last" in this example?
If the values you listed represents a single, set-based INSERT then the
server knows NOTHING about the notional "order" that you are implying to
exist. This is why I suggest your logical data model is wrong. You
apparently aren't recording enough information to solve your business
problem - so fix the design first.

A cursor won't help because a cursor's order is undefined also, unless you
specify an ORDER BY, which puts us back where we started...

1. if the order is not defined, wouldn't the natural order be the insertion
order?

2. how does an order by clause put us back where we started?

Robert



Show quoteHide quote

No, if you don't dictate an ORDER BY, SQL Server's optimizer is free to
choose any order it damn well pleases.  And since inserted is populated
internally, and you don't really have any control over how it is populated,
you can't assume that it will be in the order you inserted.  It may be the
case that you always see the behavior you expect, but it is not guaranteed,
so don't rely on it.

A

Wouldn't an order by on the inserted identity column give you the correct
insert sequence?


Show quoteHide quote

This gets more bizarre! The answer is still NO unless you insert the rows
individually because the function that generates the IDENTITY column can't
read your mind and has no idea what "sequence" you are talking about. Why
are you so reluctant to tell us what this mysterious "sequence" is? :-<

There is no "natural order" in a table. In a set-based INSERT* the order you
listed the rows in is irrelevant and is not guaranteed to be preserved in
any way whatsoever. There is no reliable way to retrieve or "get at" that
order inside the database. Only if you insert rows individually one at a
time can you say that they have an individual order.

(*I assume you are using a UNION - if not, please show us what your
set-based INSERT looks like.)

My point is just that you require an ORDER BY clause to do what you are
asking. Tell us what the order should be.

see inline

rmg66 wrote:
There is no such thing in an RDBMS as "insertion order". Order is an
attribute of data retrieval, not insertion.

No intrinsic order means that you define the order when you select data,
not when you insert it.

So without an order specified, there's no such thing as "last inserted".

Show quoteHide quote

ARRRGH!  A set has no ordering. Nada. Nyet. None.

An ORDER BY clause exist only in a cursor; cursors can be implicit or
explicit.

http://www.google.com/search?hl=en&q=ordered+sets


Web  Results 1 - 10 of about 20,300,000 for ordered sets. (0.28
seconds) 

Partially Ordered Set -- From MathWorld
A partially ordered set (or poset) is a set taken together with a
partial order
on it. Formally, a partially ordered set is defined as an ordered pair
P==(X ...
mathworld.wolfram.com/PartiallyOrderedSet.html - 21k - Dec 21, 2005 -
Cached - Similar pages

Well Ordered Set -- From MathWorld
Every finite totally ordered set is well ordered. The set of integers
Z, which
has no least element, is an example of a set that is not well ordered.
....
mathworld.wolfram.com/WellOrderedSet.html - 18k - Dec 22, 2005 -
Cached - Similar pages
[ More results from mathworld.wolfram.com ]

PlanetMath: well ordered set
A well-ordered set is a totally ordered set in which every nonempty
subset has a
.... An example of well-ordered set is the set of positive integers
with the ...
planetmath.org/encyclopedia/WellOrderedSet.html - 21k - Cached -
Similar pages

Examples for Partially Ordered Sets
Which of the following sets and their ordering relations are partially
....
It is not a well-ordered set, because the subset (0,1] has no smallest
element. ...
pirate.shu.edu/projects/ reals/infinity/answers/order1.html - 4k -
Cached - Similar pages

Basic Set Theory: A Supplement to Set Theory
An ordered set (X, <) is dense if it has at least two elements and if
for ...
The most important example of a countable dense linearly ordered set
is the ...
plato.stanford.edu/entries/set-theory/primer.html - Similar pages

Well-order - Wikipedia, the free encyclopedia
The set S together with the well-order is then called a well-ordered
set.
Roughly speaking, a well-ordered set is ordered in such a way that its
elements ...
en.wikipedia.org/wiki/Well-ordered - 15k - Cached - Similar pages

Partially Ordered Sets
We define order of a set as a relation being reflexive, antisymmetric
and transitive
in the set, partially ordered set as structure non-empty set and order
....
mizar.uwb.edu.pl/JFM/Vol1/orders_1.html - 5k - Cached - Similar pages

Partially Ordered Sets
Tom Trotter and I are writing a book on finite partially ordered sets,
which is
taking ... There are three basic models of random partially ordered
sets. ...
www.maths.lse.ac.uk/Personal/graham/research-pos.html - 24k - Cached -
Similar pages

partially ordered set: Information From Answers.com
partially ordered set In mathematics , a partially ordered set (or
poset for
short) is a set equipped with a partial order relation , formalizing
the.
www.answers.com/topic/partially-ordered-set - 31k - Cached - Similar
pages

SICStus Prolog - Ordered Set Operations
This package defines operations on ordered sets. Ordered sets are sets
represented
as lists with the elements ordered in a standard order. ...
www.sics.se/sicstus/docs/3.7.1/html/sicstus_21.html - 6k - Cached -
Similar pages


(A) see above.

(B) see 99% of the code in production today.

(C) if you need theory, think of the sort as an index rather than a
cursor, though it may indeed be implemented physically instead.

J.

So are you saying sets are ordered structures?

If you read one of the references, say,
http://mathworld.wolfram.com/TotallyOrderedSet.html then yes i think the
poster is saying that.

In maths, for which sets within the db world have there origins, there are
such things as ordered sets.

Tony

Show quoteHide quote

No, in an attempt to disprove something that is already well established,
the poster coughed up a few references on the notions of linear ordering and
full ordering in set maths -- essentially a straw man assassination attempt.

What does that have to do with the unordered nature of *sets*, which is the
topic of discussion here?

If one spends a bit more time in the same research source and the definition
of sets including its unordered nature is not hard to find. When we talk
about topics which we are not sufficiently familiar about, we tend to
reinvent definitions and mischaracterize concepts which are already well
defined and accepted in the scientific community.

Did someone disagree with that?

a better reference is here:
http://plato.stanford.edu/entries/set-theory/primer.html which makes more
sense.

Yes - Celko; if you read his post that is the reasoning.

Show quoteHide quote

a better reference is here:
http://plato.stanford.edu/entries/set-theory/primer.html which makes more
sense.

Show quoteHide quote

On Fri, 23 Dec 2005 09:27:09 -0600, "Anith Sen"
<an***@bizdatasolutions.com> wrote:
Me?  I just googled.

But to answer your question, the basic definition of a set states that
it is unordered.  No problem there.  The question is whether in
talking about relational databases we are really talking about sets,
and nothing but sets.

Celko then said:

which seems to me dead wrong, an invalid leap and invalid
generalization.

Why?

Well, let's sneak up on it.

When relational databases were being conceived, certainly the idea was
to do set operations.  But the whole point of relational databases is
to start with the mathematics of sets, and to make something useful
out of it.  Databases have semantics, and commands, and executions,
and results, and all sorts of things which are not themselves "sets".

So, we have the capability in SQLServer to get a result "set" that is
ordered, and it does not involve a cursor, unless Joe wants to suggest
that the human reading the results uses an (implied) cursor.  But that
eyeball-cursor would be used to read the unordered results, too, so it
seems an orthogonal issue.

You have a problem with any of that?

J.

I like to think of it as "when the data leaves SQL Server for the outside world, it is no longer a
relation" (1), so using Celko terms, when the data is sent to the output buffer by the execution
engine, it is a cursor.

(1), as relational as a SQL based product can be... :-)
Show quoteHide quote

On Fri, 23 Dec 2005 20:21:03 +0100, "Tibor Karaszi"
<tibor_please.no.email_kara***@hotmail.nomail.com> wrote:
Well, but I already addressed that, the output process is the same
ordered or not.

It's an interesting theoretical issue - is traditional relational
theory really clear or complete on this, or not?  And at what point do
we stop talking about relational theory and talk about these other
architectural components?

Practically, physically sorted result sets are common, depending on
just who the direct client is.

And if one wants (and gets) a sorted set for some pragmatic or
external reason, since relational theory does not depend on the
ordering, it should freely and correctly be able to ignore the
ordering for all relational purposes.

Josh

Yes, I sure liked the DECLARE CURSOR ... AS ... SELECT ... FROM inserted;

Like inserted is expected to be in the expected order, and the cursor will
run that way, too.  :-\

Let's for a moment forget about SQL, daatbases, arrays, sets, ones and zeroes.

What is the actual business case behind this question?


ML

---
http://milambda.blogspot.com/

Why query optimizer does not select the index?
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation
Deadlock updating different rows