Professional Web Applications Themes

Select Into and Trigger on Destination table.... - Microsoft SQL / MS SQL Server

I am running SQL Server 2000 Will trigger happen on each insert when doing a select into? From what I am finding out it doesn't. I just tried this and in a trigger on the destination table get a record count > 1 in the inserted table when doing a select into and the select into has more than one reocrd. I thought the inserted table would only have one record? Why is this happening? How can I work around this? Thanks, Rog...

  1. #1

    Default Select Into and Trigger on Destination table....

    I am running SQL Server 2000

    Will trigger happen on each insert when doing a select
    into?

    From what I am finding out it doesn't. I just tried this
    and in a trigger on the destination table get a record
    count > 1 in the inserted table when doing a select into
    and the select into has more than one reocrd.

    I thought the inserted table would only have one record?

    Why is this happening? How can I work around this?

    Thanks,

    Rog

    Rog Guest

  2. #2

    Default Select Into and Trigger on Destination table....

    Rog:

    Your conclusions are correct. SQL Server's triggers are
    statement level, not row level. Best solution is to code
    your triggers to support > 1 rows in inserted/deleted.

    Vern
    >-----Original Message-----
    >I am running SQL Server 2000
    >
    >Will trigger happen on each insert when doing a select
    >into?
    >
    >From what I am finding out it doesn't. I just tried this
    >and in a trigger on the destination table get a record
    >count > 1 in the inserted table when doing a select into
    >and the select into has more than one reocrd.
    >
    >I thought the inserted table would only have one record?
    >
    >Why is this happening? How can I work around this?
    >
    >Thanks,
    >
    >Rog
    >
    >.
    >
    Vern Rabe Guest

  3. #3

    Default Re: Select Into and Trigger on Destination table....

    I assume you mean INSERT... SELECT rather than SELECT INTO? You can't have a
    trigger on the target table of SELECT INTO because the table doesn't exist
    until the statement is executed.

    The trigger will fire once for each INSERT statement. If multiple rows are
    inserted with a single INSERT statement then the trigger code is executed
    once and the Inserted table contains all the rows inserted. This behaviour
    is by design.

    In what way is this a problem to you? Whatever your trigger code is doing it
    should be possible to write statements which operate on the whole Inserted
    table. If you need help post your DDL and trigger code.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Rog" <rogoflap> wrote in message
    news:022a01c347f3$48075150$a601280aphx.gbl...
    > I am running SQL Server 2000
    >
    > Will trigger happen on each insert when doing a select
    > into?
    >
    > From what I am finding out it doesn't. I just tried this
    > and in a trigger on the destination table get a record
    > count > 1 in the inserted table when doing a select into
    > and the select into has more than one reocrd.
    >
    > I thought the inserted table would only have one record?
    >
    > Why is this happening? How can I work around this?
    >
    > Thanks,
    >
    > Rog
    >

    David Portas Guest

  4. #4

    Default Re: Select Into and Trigger on Destination table....

    Its true i also face the same problem when i do the Bulk Upload thru BCP...

    I need to use this BCP and parallely insert records in some other table with further calculations...

    How do i handle this
    Shirkant Guest

Similar Threads

  1. select next id in table?
    By Jim Michaels in forum MySQL
    Replies: 1
    Last Post: April 23rd, 04:29 AM
  2. multi table select
    By Chuck Anderson in forum MySQL
    Replies: 3
    Last Post: February 10th, 06:39 AM
  3. Replies: 2
    Last Post: January 31st, 07:57 PM
  4. Trigger on v$sql catalog table
    By Jim Kennedy in forum Oracle Server
    Replies: 2
    Last Post: July 11th, 01:02 AM
  5. sending an email from a table trigger (before inserting row)
    By Holger Baer in forum Oracle Server
    Replies: 10
    Last Post: July 7th, 02:37 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