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)

new problem

I got the following error message when I do an insert trigger, can anyone
please help? Thanks.
Server: Msg 8115, Level 16, State 8, Procedure ADM_APRN_ins_upd_trg, Line
108
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.Pls post "ADM_APRN_ins_upd_trg" code, or continue using
imagination. Thanks.
Regards,
Alfred.
>--Original Message--
>I got the following error message when I do an insert
trigger, can anyone
>please help? Thanks.
>Server: Msg 8115, Level 16, State 8, Procedure
ADM_APRN_ins_upd_trg, Line
>108
>Arithmetic overflow error converting numeric to data type
numeric.
>The statement has been terminated.
>
>.
>|||Hello Alfred,
There is very little we can do here.
The problem as the error states on in line 108. I am going
to assume that it is some sort of maths function being
carried out. Whatever it is been converted into does not
like it.
Could you post that line and the 5 lines before and after
it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Pls post "ADM_APRN_ins_upd_trg" code, or continue using
>imagination. Thanks.
>Regards,
>Alfred.
>>--Original Message--
>>I got the following error message when I do an insert
>trigger, can anyone
>>please help? Thanks.
>>Server: Msg 8115, Level 16, State 8, Procedure
>ADM_APRN_ins_upd_trg, Line
>>108
>>Arithmetic overflow error converting numeric to data
type
>numeric.
>>The statement has been terminated.
>>
>>.
>.
>