New record in table already or not when in an INSTEAD OF trigger?

Hi, friends,
I am writting a INSTEAD OF INSERT trigger for an insert action on Users
table of our SQL Server 2000 DB.
I know there is only one record (the new inserted one) in inserted table,
but, is this new record also already in Users table? In anther word, if I do
a query in the trigger like:
DECLARE @.newUserID INT
SELECT @.newUserID = i.userID FROM inserted AS i
SELECT * FROM Users WHERE userID = @.newUserID
should I get a record back for the last SELECT statement, not yet? Any
reference paper?
Thanks for your help.No, the row that is in the inserted table in the INSTEAD OF TRIGGER is _not_
already in the base table. And if you don't explicitly insert the row in the
base table inside your trigger, the row will never be inserted. If you want
the behaviour you describe, you should use an AFTER trigger.
Jacco Schalkwijk
SQL Server MVP
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:22E7E45F-E28D-4D21-8E95-67C235390200@.microsoft.com...
> Hi, friends,
> I am writting a INSTEAD OF INSERT trigger for an insert action on Users
> table of our SQL Server 2000 DB.
> I know there is only one record (the new inserted one) in inserted table,
> but, is this new record also already in Users table? In anther word, if I
> do
> a query in the trigger like:
> DECLARE @.newUserID INT
> SELECT @.newUserID = i.userID FROM inserted AS i
> SELECT * FROM Users WHERE userID = @.newUserID
> should I get a record back for the last SELECT statement, not yet? Any
> reference paper?
> Thanks for your help.
>|||On Thu, 24 Mar 2005 14:37:04 -0800, Andrew wrote:

>I am writting a INSTEAD OF INSERT trigger for an insert action on Users
>table of our SQL Server 2000 DB.
>I know there is only one record (the new inserted one) in inserted table,
(snip)
Hi Andrew,
This is only true for single-row inserts. If you execute an INSERT INTO
... SELECT ... command, ALL new rows will be in the inserted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)