Re: _ID NUMBER

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 11:27:44 -0400

Great, thanks for the explanation. I have an article that recommends
SCOPE_IDENTITY() and it's the first I've heard of that there were any
limitations... 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:k7qdnSY9MZFugzPd4p2dnA@giganews.com...
> With an INSTEAD OF INSERT trigger the INSERT happens out of scope so
> SCOPE_IDENTITY is NULL
>
> CREATE TABLE Table1 (i INTEGER IDENTITY PRIMARY KEY, x INTEGER NOT NULL
> UNIQUE)
>
> GO
> CREATE TRIGGER trg_Table1 ON Table1 INSTEAD OF INSERT
>  AS
>  INSERT INTO Table1 (x)
>   SELECT x FROM Inserted
> GO
>
> INSERT INTO Table1 (x) VALUES (1)
> SELECT SCOPE_IDENTITY(), IDENT_CURRENT('Table1')
>
> Result:
>
> ---------------------------------------- ---------------------------------
--
> ----- 
> NULL                                     1
>
>
> (Tested on Enterprise, SP3)
>
> As Louis says, you can use the natural key to retrieve the value.
>
> -- 
> David Portas
> SQL Server MVP
> --
>
>

Relevant Pages

  • Re: _ID NUMBER
    ... With an INSTEAD OF INSERT trigger the INSERT happens out of scope so ... CREATE TABLE Table1 (i INTEGER IDENTITY PRIMARY KEY, ... CREATE TRIGGER trg_Table1 ON Table1 INSTEAD OF INSERT ...
    (microsoft.public.sqlserver.programming)
  • Re: Deleting a record from a database
    ... ALTER TABLE Table1 ... Create a trigger that archives the "deleted file" To do this, ... a table exactly like the original table, but you call it _History. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Key Fields
    ... Just watch out of there's a foreign key that references the table. ... A traditional trigger will ... >> Table1 has an IP Address field ... >> All records in Table2 that have the foreign key set to the original IP ...
    (microsoft.public.sqlserver.programming)
  • Re: Table compare
    ... If you want to filter on outer (Table1) criteria, ... "Steve C" wrote in message ... >> Dan Guzman ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggers
    ... I have Table1 which is the master table and Table2 which is the ... After that Table2 is dealt with. ... in Table2 come from stored procedure variables. ... What we want it to put a trigger one Table1 to do the insert into ...
    (comp.databases.oracle.server)