Do I need a cursor here

I have a web page where users can change information about themselves
and submitted to a database. The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away. The user
can then go in again and make further changes thus creating another
record in the UserChanges table. Once a day these changes are brought
down to our main database. I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day. The problem is that one of the fields is a bit field
that indicates if the email address was changed or not. This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example
ID PersonID EmailAddress EmailChange Downloaded
1 200 test@.test.com False False
2 200 blank@.blank.com True False
3 200 blank@.blank.com False False
What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.
Is this possible to do with out using a cursor?A trigger should be able to do it:
create trigger tri_UserChanges on UserChanges after insert
as
if @.@.rowcount = 0
return
update u
set
EmailChange = 1
from
UserChanges u
join inserted i on i.ID = u.ID
where exists
(
select
*
from
UserChanges u2
where
u2.PersonID = i.PersonID
and u2.ID <> i.ID
and u2.EmailChange = 1
and u2.Doenloaded = 0
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"rocky20" <goldbond_8@.hotmail.com> wrote in message
news:1140289185.842651.4140@.o13g2000cwo.googlegroups.com...
I have a web page where users can change information about themselves
and submitted to a database. The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away. The user
can then go in again and make further changes thus creating another
record in the UserChanges table. Once a day these changes are brought
down to our main database. I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day. The problem is that one of the fields is a bit field
that indicates if the email address was changed or not. This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example
ID PersonID EmailAddress EmailChange Downloaded
1 200 test@.test.com False False
2 200 blank@.blank.com True False
3 200 blank@.blank.com False False
What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.
Is this possible to do with out using a cursor?|||Just as a side note, you may want to reconsider your design. As you
can obviously see, a bit field doesn't really tell you much :) I'm
also assuming that "previous" has meaning to you, because a rows in a
table don't really have an order to them.
Here's a quick-and-dirty stab at it:
DECLARE @.s TABLE (ID int, PersonID int, EmailAddress varchar(20),
EmailChange bit, Downloaded bit)
INSERT INTO @.s
SELECT 1, 200, 'test@.test.com', 0, 0
UNION ALL
SELECT 2, 200, 'blank@.blank.com', 1, 0
UNION ALL
SELECT 3, 200, 'blank@.blank.com', 0, 0
UNION ALL
SELECT 4, 500, 'test@.test.com', 0, 0
UNION ALL
SELECT 5, 500, 'blank@.blank.com', 1, 0
UNION ALL
SELECT 6, 500, 'blank@.blank.com', 0, 1
SELECT *
FROM @.s
SELECT DISTINCT s.ID, s.PersonID, s.EmailAddress,
EmailChange = COALESCE(s2.EmailChange, s.EmailChange),
s.Downloaded
FROM @.s s LEFT JOIN @.s s2 ON s.PersonID = s2.PersonID
AND s.ID > s2.ID
AND s2.EmailChange = 1
AND s2.Downloaded = 0
WHERE s.ID IN (SELECT MAX(ID)
FROM @.s
WHERE Downloaded=0
GROUP BY PersonID)
HTH,
Stu|||rocky20 wrote:
> I have a web page where users can change information about themselves
> and submitted to a database. The database holds these changes in the
> table UserChanges. When the user completes the changes the database for
> the website is updated and changes are reflected right away. The user
> can then go in again and make further changes thus creating another
> record in the UserChanges table. Once a day these changes are brought
> down to our main database. I would like to bring down only the latest
> record for each users instead of bring down all their records they
> created that day. The problem is that one of the fields is a bit field
> that indicates if the email address was changed or not. This could
> cause problems if the user changed their email address on a previous
> record they created but not on the last one.
> Example
> ID PersonID EmailAddress EmailChange Downloaded
> 1 200 test@.test.com False False
> 2 200 blank@.blank.com True False
> 3 200 blank@.blank.com False False
>
> What I want to do is after the user submits the record
> 1.Check to see if they have any previous record that have not been
> downloaded yet
> 2.If previous records exists check to see if any of the records email
> change flag is set to True.
> 3.If it is, then update the email change flag to True in the last
> record.
> Is this possible to do with out using a cursor?
It doesn't seem like you'll need a cursor to do this. I'm not clear
about a few things though. Firstly what is/are the keys in this table?
Secondly how do we know which row is the latest? Don't use an IDENTITY
column to track the latest row. Add a DATETIME column to do that.
Finally, what's the point of the EmailChange column? It looks redundant
to me, given that you preserve the history of the email values anyway.
I think you should drop EmailChange.
Assuming you have a column to indicate the date and time of the change
you can retrieve the latest version like this:
/* Get the new email address only where the
latest version hasn't been downloaded */
SELECT emailaddress
FROM UserChanges AS U
WHERE changed_datetime =
(SELECT MAX(changed_datetime)
FROM UserChanges
WHERE personid = U.personid
HAVING MAX(changed_datetime) =
MAX(CASE WHEN downloaded = 'False' THEN changed_datetime END));
AND downloaded = 'False' /* should be 0? */
(untested)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Doesn't that hit you as "a bit" redundant? (sorry, had to do the pun)
Just over-write the old email with the new one, based on a timestamp
One of your major problems is that you do not know that fields and
records are not part of SQL; columns and rows are different creatures.
When someone logs into the routine. check to see if they have data in
the working tables. If not, copy all the old data over to your working
table. Let the user UPDATE the working data on a column by column
basis in the working database. Or if you really need to keep every
change, then add that timestamp to get the last copy.
Then do your data scrubbing and replace the old data with the new. I
would guess that a VIEW with INSTEAD OF TRIGGERs would help.
No cursors needed.