Newbie Question on SQL DB

Hello All!
I know I can do this, but not sure of the best way. I have a users table,
with a userID as key. I also have a profile table. When a user logs in,
they can add to thier profile if they want. My question is, What is the bes
t
way to link that user ID so the user ID is filled in on the userID col in th
e
profile table, and the user profile sticks to that ID. Even if the user
comes back to add to it later.
I think I would have to return the value of the user ID, pass that forward
to the profile table, but I'm not sure.
TIA!!!
RudyRudy wrote:
> Hello All!
> I know I can do this, but not sure of the best way. I have a users
> table, with a userID as key. I also have a profile table. When a
> user logs in, they can add to thier profile if they want. My
> question is, What is the best way to link that user ID so the user ID
> is filled in on the userID col in the profile table, and the user
> profile sticks to that ID. Even if the user comes back to add to it
> later.
> I think I would have to return the value of the user ID, pass that
> forward to the profile table, but I'm not sure.
> TIA!!!
>
> Rudy
Create Table MyUsers (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
UserName NVARCHAR(50))
Create Table UserProfiler (
UserID INT NOT NULL REFERENCES MyUsers(UserID),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
Not sure of your design, but assuming you had a relationship like the
above, you need to physically insert the UserID into the UserProfile
table. There is not way for SQL Server to know what UserID you want
interted, unless you're talking about a login name (are you?).
For a login name you could use suser_sname() and have it as the default
on the table:
Create Table UserProfile (
UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
and then use:
Insert UserProfile (
OptionID, OptionValue)
Values (
50, N'Profiler Data')
if the user id is something your database stores separately from the
login name, you would need to send the value to SQL Server. So you might
grab the UserID when the user logs into the application and pass it to
the insert statement or pass it to a stored procedure to be inserted
into the profile table.
David Gugick
Imceda Software
www.imceda.com|||Hi David!
Thank you for the quick reply. So I am talking about a login name, and the
user ID is on the same table as the user name.
For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
I don't understand what you mean by the suser_sname as the default. It's
been awhile since I had to work with SQL, and was just learning at that time
.
Now that I need to use SQL a little bit more indepth than just making simple
tables, and passing values back and forth, I'm kinda in the weeds if you kno
w
what I mean. LOL
So now that I have set you up for my ignorance, how does one refrence? I
know about relationships and stuff, sorta. And I know I can use views to hav
e
data update automaticly from other tables. And views can be used just like
tables, right? Would I create a FK between the two tables using the userID?
But that doesn't update or keep the information of the userID the same, does
it?
I though if I could just return a value to what user was logged on, and then
that userID would link with the profile table, andthen the info can be
update. Maybe it would be easier if I had a table for just users who are
logged on?
Am I way off base or what?
Thank you for your time David!
Rudy
"David Gugick" wrote:

> Rudy wrote:
> Create Table MyUsers (
> UserID INT IDENTITY NOT NULL PRIMARY KEY,
> UserName NVARCHAR(50))
> Create Table UserProfiler (
> UserID INT NOT NULL REFERENCES MyUsers(UserID),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
>
> Not sure of your design, but assuming you had a relationship like the
> above, you need to physically insert the UserID into the UserProfile
> table. There is not way for SQL Server to know what UserID you want
> interted, unless you're talking about a login name (are you?).
> For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
> and then use:
> Insert UserProfile (
> OptionID, OptionValue)
> Values (
> 50, N'Profiler Data')
>
> if the user id is something your database stores separately from the
> login name, you would need to send the value to SQL Server. So you might
> grab the UserID when the user logs into the application and pass it to
> the insert statement or pass it to a stored procedure to be inserted
> into the profile table.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Rudy wrote:
> I don't understand what you mean by the suser_sname as the default.
suser_sname() is a function that returns the logged in user name.
My example showed a PK/FK reference. And as I mentioned, the FK value
does not update automatically, it just enforces values based on the
available PK values in the referenced table.
I think you need to spell out in a clear and concise way exactly what
you are trying to do, what all the data means, etc.
David Gugick
Imceda Software
www.imceda.com

Newbie on Triggers

System Sql server 2000

Hi all

I've dipped my toe in stored procedures, I'm now having a look at triggers

If I had 2 tables and I wanted to update table 2 with some data from table
1, every time table 1 has a new entry made in it (e.g. for an audit trail) I
thought I would be able to use a trigger on table 1.

for instance

on insert of new value in table 1 execute a trigger

The trigger gets the value of the newly created primary key in table 1 (the
primary key is generated by Autonumber) and inserts a copy of the primary
key into table 2

I thought the following would work

ALTER TRIGGER update_table2
ON dbo.table1
FOR INSERT AS
begin
declare @.new_key as bigint
set @.new_key = dbo.table1(PK_test_primary_key)

INSERT INTO dbo.table2
(FK_from_table1)
VALUES (@.new_key)
end

unfortunatly I cant read the value of the newly entered primary key from
table 1. I get a message saying PK_test_primary_key doesn't exist in
dbo.table1

does anyone know if what I am trying to achieve is possible, and maybe a
clue as a good way to go about doing it?

many thanks

AndyHi just a bit more...

after some more googling I have found @.@.identity and it seems to give the
identity of the last added record in the table calling the trigger

is this correct, and is it safe to do this i.e.

ALTER TRIGGER update_table2
ON dbo.table1
FOR INSERT AS
begin

INSERT INTO dbo.table2
(FK_from_table1)
VALUES (@.@.identity)
end

I've also been playing with objConn.BeginTrans in my vb6 code and I write
lots of records to table 1 before confirming the transaction. I believe that
the autonumbers are only generated on completion of the transaction, so how
many triggers will be executed (one per transaction or 1 per new record) and
at what time (as the records are written or on compltion of the
transaction)... Will it reliably update table 2 with the correct info from
table 1 if say dozens of additions are made to table 1 from different
sources at the same time...?

I think I worry too much and it seems to work when I test it, but I like to
know what happens when 8-)

thanks again

Andy

"aaj" <a.b@.c.com> wrote in message
news:4028f77b$0$29799$afc38c87@.news.easynet.co.uk. ..
> System Sql server 2000
> Hi all
> I've dipped my toe in stored procedures, I'm now having a look at triggers
> If I had 2 tables and I wanted to update table 2 with some data from table
> 1, every time table 1 has a new entry made in it (e.g. for an audit trail)
I
> thought I would be able to use a trigger on table 1.
> for instance
> on insert of new value in table 1 execute a trigger
> The trigger gets the value of the newly created primary key in table 1
(the
> primary key is generated by Autonumber) and inserts a copy of the primary
> key into table 2
> I thought the following would work
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> declare @.new_key as bigint
> set @.new_key = dbo.table1(PK_test_primary_key)
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.new_key)
> end
> unfortunatly I cant read the value of the newly entered primary key from
> table 1. I get a message saying PK_test_primary_key doesn't exist in
> dbo.table1
> does anyone know if what I am trying to achieve is possible, and maybe a
> clue as a good way to go about doing it?
> many thanks
> Andy|||aaj (a.b@.c.com) writes:
> after some more googling I have found @.@.identity and it seems to give the
> identity of the last added record in the table calling the trigger
> is this correct, and is it safe to do this i.e.
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.@.identity)
> end

No, this is not the way to go. In a trigger you have access to two
virtual tables, inserted and deleted. inserted holds the rows that
were inserted, and in case of an UPDATE statment, the values after
the update. And deleted holds the values that were removed by a DELETE
or an UPDATE statement.

Beware that trigger fires once by statement, so many rows can be affected
at once.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> declare @.new_key as bigint
> set @.new_key = dbo.table1(PK_test_primary_key)
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.new_key)
> end
Triggers utilize the virtual "inserted" and "deleted" tables. Check
out this article:http://www.sqlteam.com/item.asp?ItemID=3850.|||You should also not that the @.@.identity is not just the last identity
created for that table, it is the last identity created. That makes the
value of @.@.identity sort of unpredictable. The other guys are right that in
the trigger you should use INSERTED and DELETED tables, but if you need to
access the latest identity created within a given stored procedure you can
use SCOPE_IDENTITY().

"aaj" <a.b@.c.com> wrote in message
news:4028fa81$0$29827$afc38c87@.news.easynet.co.uk. ..
> Hi just a bit more...
> after some more googling I have found @.@.identity and it seems to give the
> identity of the last added record in the table calling the trigger
> is this correct, and is it safe to do this i.e.
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.@.identity)
> end
>
> I've also been playing with objConn.BeginTrans in my vb6 code and I write
> lots of records to table 1 before confirming the transaction. I believe
that
> the autonumbers are only generated on completion of the transaction, so
how
> many triggers will be executed (one per transaction or 1 per new record)
and
> at what time (as the records are written or on compltion of the
> transaction)... Will it reliably update table 2 with the correct info
from
> table 1 if say dozens of additions are made to table 1 from different
> sources at the same time...?
> I think I worry too much and it seems to work when I test it, but I like
to
> know what happens when 8-)
> thanks again
> Andy
>
> "aaj" <a.b@.c.com> wrote in message
> news:4028f77b$0$29799$afc38c87@.news.easynet.co.uk. ..
> > System Sql server 2000
> > Hi all
> > I've dipped my toe in stored procedures, I'm now having a look at
triggers
> > If I had 2 tables and I wanted to update table 2 with some data from
table
> > 1, every time table 1 has a new entry made in it (e.g. for an audit
trail)
> I
> > thought I would be able to use a trigger on table 1.
> > for instance
> > on insert of new value in table 1 execute a trigger
> > The trigger gets the value of the newly created primary key in table 1
> (the
> > primary key is generated by Autonumber) and inserts a copy of the
primary
> > key into table 2
> > I thought the following would work
> > ALTER TRIGGER update_table2
> > ON dbo.table1
> > FOR INSERT AS
> > begin
> > declare @.new_key as bigint
> > set @.new_key = dbo.table1(PK_test_primary_key)
> > INSERT INTO dbo.table2
> > (FK_from_table1)
> > VALUES (@.new_key)
> > end
> > unfortunatly I cant read the value of the newly entered primary key from
> > table 1. I get a message saying PK_test_primary_key doesn't exist in
> > dbo.table1
> > does anyone know if what I am trying to achieve is possible, and maybe a
> > clue as a good way to go about doing it?
> > many thanks
> > Andy|||Jason Sauer (jason.sauer@.fuse.net) writes:
> You should also not that the @.@.identity is not just the last identity
> created for that table, it is the last identity created. That makes the
> value of @.@.identity sort of unpredictable. The other guys are right that
> in the trigger you should use INSERTED and DELETED tables, but if you
> need to access the latest identity created within a given stored
> procedure you can use SCOPE_IDENTITY().

In this particular case @.@.identity is the only choice. scope_identity()
will return NULL, because there have been no inserts into a table
with an identity column in the current scope, that is the trigger.

The only time @.@.identity will not return the right value, is when there
are more than one trigger on the table, and the other trigger executes
first and that trigger too inserts into a table with a identity column.
Maybe not the most likely scenario.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Newbie HELP (ON SQL)

Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lostohhh found it

SELECT Whatever, WhateverElse FROM TableName WHERE DateField >= DATEADD(d, -7, GETDATE())

Originally posted by webstep
Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lost|||If you put this at the top of your script:

declare @.date1 datetime, @.date2 datetime
set @.date2 = convert(varchar(11), getdate(), 111)
set @.date1 = convert(varchar(11), getdate()-7, 111)

...then you can just use @.date1 as your from date and @.date2 as your to date. You won't have to put in any dates.

Steve

Newbie Connection problems

Hi All
I am running a website on one server and need to be able to connect to
SQL 2000 on another server. When I try to connect to the SQL server
in an asp.net application I get the following error:
Login failed for user 'Domain\Server$'
How do I configure SQL to allow the other IIS server to connect.
TIA
Tony
Tony,
please can you post up your connectionstring that the SQLConnection object
is using. Also, do you want to use SQL authentication or Trusted, and what
is your SQL Server configured to allow - Mixed or Windows.
TIA,
Paul Ibison
|||From the error message it appears that the machine account is attempting to
connect to SQL Server, not a user account. You can get the connection to
work by adding a login for the machine account: Domain\server$. This is a
AD accounta nd many application use it to connect to SQL Server. It is
basically the machine system account.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Newbie Connection problems

Hi All
I am running a website on one server and need to be able to connect to
SQL 2000 on another server. When I try to connect to the SQL server
in an asp.net application I get the following error:
Login failed for user 'Domain\Server$'
How do I configure SQL to allow the other IIS server to connect.
TIA
TonyTony,
please can you post up your connectionstring that the SQLConnection object
is using. Also, do you want to use SQL authentication or Trusted, and what
is your SQL Server configured to allow - Mixed or Windows.
TIA,
Paul Ibison|||From the error message it appears that the machine account is attempting to
connect to SQL Server, not a user account. You can get the connection to
work by adding a login for the machine account: Domain\server$. This is a
AD accounta nd many application use it to connect to SQL Server. It is
basically the machine system account.
Rand
This posting is provided "as is" with no warranties and confers no rights.