Inserted and deleted temp tables ?

I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables.

I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages.

The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html

I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced.

Thanks in advance.If memory serves, the INSERTED and DELETED tables are special objects that belong to each session. So if two users update at the same time you should have two copies of these virtual tables

User1INSERTED/DELETED
User2INSERTED/DELETED

Brent

Inserted & Deleted Tables!

Suppose a trigger gets fired when the following UPDATE query gets
executed:
---
UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
---
Now the Inserted table will have the new record '12345' in the Pwd
column & the Deleted table will have the old record '11111' in the Pwd
column. So will the record 'jack' exist in the UserID column of both
the Inserted table & the Deleted table that the trigger will be making
use of?
Thanks,
ArpanHi
Yes, the whole row, as it was before and after are in the respective tables,
not just the column that changed.
If you update the primary key of a table, then comparing the Inserted and
Deleted becomes very difficult.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123800474.961292.259990@.g49g2000cwa.googlegroups.com...
> Suppose a trigger gets fired when the following UPDATE query gets
> executed:
> ---
> UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
> ---
> Now the Inserted table will have the new record '12345' in the Pwd
> column & the Deleted table will have the old record '11111' in the Pwd
> column. So will the record 'jack' exist in the UserID column of both
> the Inserted table & the Deleted table that the trigger will be making
> use of?
> Thanks,
> Arpan
>|||On 11 Aug 2005 15:47:55 -0700, Arpan wrote:

>Suppose a trigger gets fired when the following UPDATE query gets
>executed:
>---
>UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
>---
>Now the Inserted table will have the new record '12345' in the Pwd
>column & the Deleted table will have the old record '11111' in the Pwd
>column. So will the record 'jack' exist in the UserID column of both
>the Inserted table & the Deleted table that the trigger will be making
>use of?
Hi Arpan,
Almost.
The exact correct way to put this is:
- The deleted table will hold 0, 1, or many rows that all have UserID
'jack' and Pwd '11111'. Impossible to tell what the other columns will
be.
- The inserted table will hold 0, 1, or many rows (but the same number
as the deleted table) that all have UserID 'jack' and Pwd '12345'; the
other columns will be the same as in the corresponding rows in the
deleted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert xml into mssql 2000

Hi,

I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.

What is the most efficient way to do this using C#2005 and mssql2000?

Any good article or tutorial?

http://www.databasejournal.com/features/mssql/article.php/2204421|||http://aspalliance.com/477

Insert with Parameters (SQL Server)

Hello, this is my code:

SqlCommand cmd =new SqlCommand("INSERT INTO Users (Username,Password) " +"VALUES ('@.username','@.password' ",new SqlConnection(my_ConnectionString)); cmd.Parameters.Add("@.username", SqlDbType.NVarChar, 50).Value = txtUsername.Text;cmd.Parameters.Add("@.password", SqlDbType.NVarChar, 50).Value = txtPassword.Text cmd.Connection.Open();cmd.ExecuteNonQuery();cmd.Connection.Close();

But in the database, the row inserted is exactly this:

"@.username" "@.password"

I mean, the parameters are not inserted :S

Please, tell me the error in the code...
Thank you so much,

Carlos.Placing single quotes around the parameters makes them be treated as literal strings. Remove the single quotes and you should have better luck.|||Thank you, now it works ;)

Carlos.

insert then update...

greetings
I am developing an application for the marketing dept at my company.Basically users can build the content of an email to be sent to oursubscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String ="INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)VALUES ('" & txtCampaignName.Text & "','" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &DestinationsSelected & "','" & FreeTextBox2.Text & "', '"& txtBannerPath.Text & "')SELECT @.@.IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String ="UPDATE CZC_email SET SendDate = '" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"& txtCampaignName.Text & "',Destinations = '" &DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text& "' WHERE CZ_ID = @.@.IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers

(1) Use a stored proc.
(2) Use SCOPE_IDENTITY() instead of @.@.IDENTIY. Check books on line for the differences.
(3) Use Parameterized Queries to prevent SQL Injection atatcks (google for more info on this).|||Save the @.@.IDENTITY values you get from insert command & then in the update command pass the value returned from the insertion instead of @.@.IDENTITY|||I've tried saving the @.@.identity and scope_identity as a value ofvariable varCZ_ID by using the following code (i'm using the MS DAAB)
varCZ_ID = dataReader("SCOPE_IDENTITY")
or

varCZ_ID = dataReader("CZ_ID")

however, this is erroring 'Invalid attempt to read when no data is present.'
Any ideas what I'm doing wrong? this is really doing my head in!
Thanks
|||If you use a stored proc you could save a trip to the server and get back accurate Id.
|||yes I agree, I just wanted to get it working first off.
I managed to fix the problem by saving the @.@.IDENTITY into a session variable.

|||@.@.IDENTITY does not always give you the identity value that just gotgenerated by the insert statement. If multiple calls were made at thesame time it could mix up the Id's. So it is advised to useSCOPE_DENTITY() instead of @.@.IDENTITY.
|||Thats a good point, and something I am aware off.
For this particular application is not a big deal, as it only going to be used by two people - and not at the sametime.
However i will look to improve it soon, and that will be things I will do.
Cheers

Insert Statement

I am trying to complete an insert statement for user logins in order to keep
a record of all the times users login. Can someone help me with the statemen
t
below. Thanks very much for any help anyone can give me.
CREATE PROCEDURE [LoginValidation]
@.p_UserName char(12)
AS
SELECT UserName, Password, User_ID
FROM LoginDetails
WHERE UserName = @.p_UserName
--Want to insert a row in a LoginStats table to record user logins
GO
Here is something like the Insert statement which I require.
Can someone help me to complete this.
INSERT LoginStats
(
LoginID,
User_ID,
LoginTime
)
VALUES
(
How do I insert an automatic primary key,
how do i insert the User_Id from the previous query,
GETDATE()
)
Table : LoginStats
Column Name Data Type
(PK)LoginID char
(FK)User_ID char
LoginTime timestampHi
CREATE PROCEDURE [LoginValidation]
@.p_UserName char(12)
AS
SET NOCOUNT ON
INSERT LoginStats
(
LoginID,
User_ID,
LoginTime
)
SELECT
User_ID,
UserName,
GETDATE()
FROM LoginDetails
WHERE UserName = @.p_UserName
Regards
Mike
"Stephen" wrote:

> I am trying to complete an insert statement for user logins in order to ke
ep
> a record of all the times users login. Can someone help me with the statem
ent
> below. Thanks very much for any help anyone can give me.
> CREATE PROCEDURE [LoginValidation]
> @.p_UserName char(12)
> AS
> SELECT UserName, Password, User_ID
> FROM LoginDetails
> WHERE UserName = @.p_UserName
> --Want to insert a row in a LoginStats table to record user logins
> GO
> Here is something like the Insert statement which I require.
> Can someone help me to complete this.
> INSERT LoginStats
> (
> LoginID,
> User_ID,
> LoginTime
> )
> VALUES
> (
> How do I insert an automatic primary key,
> how do i insert the User_Id from the previous query,
> GETDATE()
> )
> Table : LoginStats
> Column Name Data Type
> (PK)LoginID char
> (FK)User_ID char
> LoginTime timestamp|||Stefen
If you change the LoginId to an Identity property run the following exampe
INSERT LoginStats
(
User_ID,
LoginTime
)
SELECT User_ID,GETDATE(),
FROM LoginDetails
WHERE UserName = @.p_UserName
AND NOT EXISTS
(
SELECT * FROM LoginStats WHERE LoginStats.User_ID=LoginDetails.User_ID
)
Or if LoginId is INTEGER run this one
INSERT LoginStats
(
LoginId
User_ID,
LoginTime
)
SELECT (SELECT COALESCE(MAX(LoginId),0)+1 FROM LoginStats ) ,
User_ID,GETDATE(),
FROM LoginDetails
WHERE UserName = @.p_UserName
AND NOT EXISTS
(
SELECT * FROM LoginStats WHERE LoginStats.User_ID=LoginDetails.User_ID
)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:36B4384E-F8FC-48BD-8134-4585D8A7C6C0@.microsoft.com...
> I am trying to complete an insert statement for user logins in order to
keep
> a record of all the times users login. Can someone help me with the
statement
> below. Thanks very much for any help anyone can give me.
> CREATE PROCEDURE [LoginValidation]
> @.p_UserName char(12)
> AS
> SELECT UserName, Password, User_ID
> FROM LoginDetails
> WHERE UserName = @.p_UserName
> --Want to insert a row in a LoginStats table to record user logins
> GO
> Here is something like the Insert statement which I require.
> Can someone help me to complete this.
> INSERT LoginStats
> (
> LoginID,
> User_ID,
> LoginTime
> )
> VALUES
> (
> How do I insert an automatic primary key,
> how do i insert the User_Id from the previous query,
> GETDATE()
> )
> Table : LoginStats
> Column Name Data Type
> (PK)LoginID char
> (FK)User_ID char
> LoginTime timestamp|||What is "loginid" supposed to represent? It looks redundant to me. Your
pseudo- table structure drescribes LoginTime as a timestamp but I guess
you will want a DATETIME. The TIMESTAMP datatype has nothing to do with
recording date and time.
I guess you want something like this:
CREATE TABLE LoginStats (userid INTEGER NOT NULL REFERENCES Users
(userid), logintime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (userid,logintime))
SET @.userid =
(SELECT userid
FROM Users
WHERE username = @.p_username)
INSERT INTO LoginStats (userid)
SELECT @.userid
Since your SP presumably returns only one row you should find it more
efficient to return the result as output parameters rather than a
result set. Why are you returning the password as well? Don't store
passwords in the database - encrypted or otherwise - password recovery
mechanisms are inherent security flaws. Store only a secure hash of the
password and validate it in the middle tier. Finally, have you
considered using SQL Server's built in security and audit, which will
automatically record logins for you?
David Portas
SQL Server MVP
--|||You can create an IDENTITY column on your table to have an autoincrementing
column, that you could use as a primary key, but considering that the
combination of LoginTime and User_ID is unique already (unless your users
are really, really, really fast ;-)), you don't really need that.
So you can use the following:
CREATE TABLE LoginStats (
LoginTime DATETIME NOT NULL,
User_ID INT NOT NULL,
CONSTRAINT PK_LoginStats
PRIMARY KEY (LoginTime , User_ID),
CONSTRAINT FK_LoginStats__Users
FOREIGN KEY (User_ID)
REFERENCES Users (User_ID)
)
CREATE PROCEDURE [LoginValidation]
@.p_UserName char(12)
AS
SELECT UserName, Password, User_ID
FROM LoginDetails
WHERE UserName = @.p_UserName
INSERT INTO LoginStats (LoginTime, User_ID)
SELECT GETDATE(), User_ID
FROM LoginDetails
WHERE UserName = @.p_UserName
Jacco Schalkwijk
SQL Server MVP
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:36B4384E-F8FC-48BD-8134-4585D8A7C6C0@.microsoft.com...
>I am trying to complete an insert statement for user logins in order to
>keep
> a record of all the times users login. Can someone help me with the
> statement
> below. Thanks very much for any help anyone can give me.
> CREATE PROCEDURE [LoginValidation]
> @.p_UserName char(12)
> AS
> SELECT UserName, Password, User_ID
> FROM LoginDetails
> WHERE UserName = @.p_UserName
> --Want to insert a row in a LoginStats table to record user logins
> GO
> Here is something like the Insert statement which I require.
> Can someone help me to complete this.
> INSERT LoginStats
> (
> LoginID,
> User_ID,
> LoginTime
> )
> VALUES
> (
> How do I insert an automatic primary key,
> how do i insert the User_Id from the previous query,
> GETDATE()
> )
> Table : LoginStats
> Column Name Data Type
> (PK)LoginID char
> (FK)User_ID char
> LoginTime timestamp|||To be honest i was trying something simple but i understand that its probabl
y
not the best way for me to do things. In fact its possibly a bad way to do
things. I have my Password stored in a table in the DB and I check this
password in my code behind of my web application. I'm the only one with
access to the Database but i know its still not the most secure way. What
ways does SQL Server Built in security work. Do you have any example or link
s
which can help me do a simple database which stores user details and has
stored procedures for logins etc. I'm not sure of all the tables I should
have and what procedures I should write etc. Any help at all with this would
be very grateful.
In case you were wondering at the moment I basically have a datareader and i
check the reader and see if the password matches for the username entered. I
f
it does I allow the user to proceed otherwise a bring a message up saying
incorrect details. It works ok but I know its far from the best way of doing
it and i'm struggling to find better examples which aren't to difficult whic
h
I can use. Have you any suggestions. I did a google search on example
databases with login tables and stored procedures but didn't get much joy
unfortunately.
"David Portas" wrote:

> What is "loginid" supposed to represent? It looks redundant to me. Your
> pseudo- table structure drescribes LoginTime as a timestamp but I guess
> you will want a DATETIME. The TIMESTAMP datatype has nothing to do with
> recording date and time.
> I guess you want something like this:
> CREATE TABLE LoginStats (userid INTEGER NOT NULL REFERENCES Users
> (userid), logintime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
> PRIMARY KEY (userid,logintime))
> SET @.userid =
> (SELECT userid
> FROM Users
> WHERE username = @.p_username)
> INSERT INTO LoginStats (userid)
> SELECT @.userid
> Since your SP presumably returns only one row you should find it more
> efficient to return the result as output parameters rather than a
> result set. Why are you returning the password as well? Don't store
> passwords in the database - encrypted or otherwise - password recovery
> mechanisms are inherent security flaws. Store only a secure hash of the
> password and validate it in the middle tier. Finally, have you
> considered using SQL Server's built in security and audit, which will
> automatically record logins for you?
> --
> David Portas
> SQL Server MVP
> --
>|||If it's a public-facing web app where you can't otherwise authenticate users
then you will need to roll your own authentication. You can utilize the
cryptographic .NET classes to hash passwords for storing in the database and
the MSDN docs on these include examples of how to hash and salt passwords.
If it's an Intranet app then you may be able to use Windows or SQLServer
authentication, in which case you can take advantage of built in security an
d
turn on auditing of logins. To do this select the Security tab of the Server
Properties dialogue in EM and choose the required Audit Level option. Logins
will be recorded in the application log.
David Portas
SQL Server MVP
--

insert record from another database

hi. we have 1 production database and a 2nd backup database. one of our users accidentally deleted a case and we would like to get it back. our production database is updated nightly so none of the case's information would've changed.

how can i insert the data from the backup database to the production database? i need to copy one row. thanks for your help!just one row?

how about a simple insert statement?