Professional Web Applications Themes

Can trigger return an identity value - Microsoft SQL / MS SQL Server

Triggers can not return values, Identity or otherwise. You can look at using Identity (or SCOPE_IDENTITY if 2000) from the stored procedure that invoked it. But if you need the value you should look at using your own generated ID since using the method with the trigger would fail if you had more than 1 row inserted at a time. -- Andrew J. Kelly SQL Server MVP "Ricky" <ricky.arorametc.state.mn.us> wrote in message news:0be601c33f57$bd137720$a001280aphx.gbl... > I have defined an Update trigger on table PITRY. The > trigger is inserting records into an PITRYAuditHistory > table and at the end I do ...

  1. #1

    Default Re: Can trigger return an identity value

    Triggers can not return values, Identity or otherwise. You can look at
    using Identity (or SCOPE_IDENTITY if 2000) from the stored procedure that
    invoked it. But if you need the value you should look at using your own
    generated ID since using the method with the trigger would fail if you had
    more than 1 row inserted at a time.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Ricky" <ricky.arorametc.state.mn.us> wrote in message
    news:0be601c33f57$bd137720$a001280aphx.gbl...
    > I have defined an Update trigger on table PITRY. The
    > trigger is inserting records into an PITRYAuditHistory
    > table and at the end I do have identity value for the
    > last inserted record in audit history table.
    >
    > Can I return this identity value back into stored
    > procedure which issued the update statement to begin
    > with...
    >
    > Thanks in advance
    >
    > Ricky

    Andrew J. Kelly Guest

  2. #2

    Default Re: Can trigger return an identity value

    Just to add to Andrew's post, you could return the IDENTITY value as a
    result to the calling application. For example, SELECT IDENTITY will
    result in the identity value being returned the client application as a
    recordset.

    --
    HTH,
    Vyas, MVP (SQL Server)
    [url]http://vyaskn.tripod.com/[/url]
    What hardware is your SQL Server running on?
    [url]http://vyaskn.tripod.com/poll.htm[/url]




    "Ricky" <ricky.arorametc.state.mn.us> wrote in message
    news:0be601c33f57$bd137720$a001280aphx.gbl...
    I have defined an Update trigger on table PITRY. The
    trigger is inserting records into an PITRYAuditHistory
    table and at the end I do have identity value for the
    last inserted record in audit history table.

    Can I return this identity value back into stored
    procedure which issued the update statement to begin
    with...

    Thanks in advance

    Ricky


    Narayana Vyas Kondreddi Guest

Similar Threads

  1. Identity flow
    By dotnetguy in forum ASP.NET Web Services
    Replies: 0
    Last Post: June 23rd, 11:55 AM
  2. ASP.NET Identity
    By CN in forum ASP.NET Web Services
    Replies: 0
    Last Post: January 5th, 06:08 PM
  3. IDENTITY is 0 ?
    By A Ratcliffe in forum ASP Database
    Replies: 6
    Last Post: October 22nd, 12:26 PM
  4. Recordset doesn't return identity field
    By A_X_L_V in forum ASP Components
    Replies: 7
    Last Post: October 21st, 02:48 PM
  5. retrieving identity
    By JoŽl in forum ASP.NET General
    Replies: 4
    Last Post: June 30th, 07:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139