Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

I have an insert trigger that works just fine when I insert one row
When I perfrom a bulk insert, the trigger only seems to work on the last row 
in the input file.
What should the trigger be doing to process each row of the inserted data?

Cheers

Bob 


0
thing
12/9/2009 12:16:41 AM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
957 Views

Similar Articles

[PageSpeed] 35

What's your trigger code look like?

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"thing" <someone@microsoft.com> wrote in message 
news:O4c4cSGeKHA.3792@TK2MSFTNGP02.phx.gbl...
>I have an insert trigger that works just fine when I insert one row
> When I perfrom a bulk insert, the trigger only seems to work on the last 
> row in the input file.
> What should the trigger be doing to process each row of the inserted data?
>
> Cheers
>
> Bob
> 

0
Michael
12/9/2009 12:37:27 AM
thing (someone@microsoft.com) writes:
> I have an insert trigger that works just fine when I insert one row When
> I perfrom a bulk insert, the trigger only seems to work on the last row 
> in the input file. 
> What should the trigger be doing to process each row of the inserted data?
 
Sounds like you have coded trigger like:

   SELECT @var1 = col1, @var2 = col2 .. FROM inserted

Since triggers fires once per statement, a trigger written like this will
only handle one row when you do multirow inserts.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
12/9/2009 8:11:37 AM
Thanks Erland,

I put in a cursor and a loop and now it works as expected

Bob
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CDC5D8565C77Yazorman@127.0.0.1...
> thing (someone@microsoft.com) writes:
>> I have an insert trigger that works just fine when I insert one row When
>> I perfrom a bulk insert, the trigger only seems to work on the last row
>> in the input file.
>> What should the trigger be doing to process each row of the inserted 
>> data?
>
> Sounds like you have coded trigger like:
>
>   SELECT @var1 = col1, @var2 = col2 .. FROM inserted
>
> Since triggers fires once per statement, a trigger written like this will
> only handle one row when you do multirow inserts.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 


0
thing
12/10/2009 12:10:50 AM
thing (someone@microsoft.com) writes:
> I put in a cursor and a loop and now it works as expected
 
I don't know what you trigger does, but you should probably have found
a set-based solution for better performance.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
12/10/2009 10:42:06 PM
Reply:

Similar Artilces:

Regarding button dosen't work
Regarding button dosen't work on none of the Outlook client CRM forms. Problem is just on one machine. CRM 1.2 Try clearing the IE cache on that machine. Gill >-----Original Message----- >Regarding button dosen't work on none of the Outlook >client CRM forms. Problem is just on one machine. CRM 1.2 >. > Thanks for reply but didn't work. Any other ideas? >-----Original Message----- >Try clearing the IE cache on that machine. > >Gill >>-----Original Message----- >>Regarding button dosen't work on none of the Outlook >>client C...

Inserting Navigatable Calendars into Excel
Using Excel 2000 in a Windows 2000 O/S, is it possible to generate a calendar and be able to navigate from one month to the next using a drop-down selection list? Thank you. -- LPS LPS See Ron de Bruin's site for Calendar instructions. http://www.rondebruin.nl/calendar.htm Gord Dibben Excel MVP On Fri, 21 Jan 2005 07:37:02 -0800, LPS <LPS@discussions.microsoft.com> wrote: >Using Excel 2000 in a Windows 2000 O/S, is it possible to generate a calendar >and be able to navigate from one month to the next using a drop-down >selection list? > >Thank you. ...

Folders Appearing Empty
I have an archive file that is giving problems. When I open this archive all folders display as empty. However, there are 6000 odd messages and the file is several hundred meg. I have run scanpst and it reports no errors found. Outlook version is 2000 SP3 . Thank you Regards, James Does he have a view applied to his Inbox, such as "Unread Messages" or perhaps a filter? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to ...

Work out overtime hours for individuals
Hi All I have a question which i am hoping someone will eb able to help me with, i have a spreadsheet which contains infromation for part time hours worked during the course of a month for individuals (e.g. below) Name Start End Hours Persona 1:30 2:30 1 Persona 3:30 4:30 1 Persona 9:30 11:30 person b person b person c person c person d etc etc What i need is a formula that will allo wme to add an extra column which gives me the total hours person a worked, person be worked and so on. The original data contains multiple lines (i.e persona could have 10 lines, personb15 lines etc etc), and...

Insert page from another Publisher file
I am trying to insert a page from one Publisher doc into another Publisher doc. I don't want to COPY/PASTE since that would mean I would have to reformatt the text. Help. If it is only one page, use the Painter tool. Open help and read how to use it. Copy/paste from one Publisher document to another doesn't change the formatting, have you tried? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Mark Davies" <Mark Davies@discussions.microsoft.com> wrote in message news:4FB773EB-AE03-484E-8818-EED12FFAC0FB@mi...

Automatic reply does not work
I am trying to set Outlook to automatically reply with a template to any message with certains words in the subject line but it does not work. When I receive a message that the rule should apply to I get a popup window "Rules in Error" that says Cannot reply to message. I have tried everything I know but can't get the auto reply to send the message. Thanks for helping. For the benefit of others who may find this post (since you know the answer from another post) - it’s a bug in Outlook 2007 SP2. Best advice going forward is to make sure you have the latest upda...

simple query not working
Hi: Trying to set up a simple query, and can't figure out why its not working. Hope someone can help me think a little more clearly. Trying to link two tables: 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode' 2)tblCodes, with fields 'SvcCode', and 'SvcDescription' Join is 'SvcCode' Query design is to show ClientID, DateofSvc, SvcCode, and SvcDescription. SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc, tblCodes.SvcCode, tblCodes.Description FROM tblSERVICES INNER JOIN tblCodes ON tblSERVICES.SvcCode = tblCodes...

insert picture on the receipt
Hello. I am trying to insert company logo (JPEG file) into the receipt. Is there anyway We can insert the picture into the receipt? I see 'Logo Filename (Header)' section in the receipt property. How should I insert it? Where should I save the file and how can i connect it to the receipt? Can JPEG file loaded into the system? If not, which file type should I use? Please be advised. thank you. On Nov 12, 11:46=A0am, Alex <blacksk...@gmail.com> wrote: > Hello. > I am trying to insert company logo (JPEG file) into the receipt. > Is there anyway We can insert the picture i...

Auto Correct in Outlook will not work
I have Outlook 2007 and Word 2007 and the auto correct feature will not work in Outlook or Word. I can add and see the entry in auto correct in both programs. However, when I try and use it, nothing happens. I am trying to get Fyi to turn into F.Y.I. You need to set the auto-correct options in Outlook 2007. 2007 no = longer depends on Word for its auto-correct options. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, di...

AutoNumber Reset Doesn't Work in XP
We are running Access 2000 against a database on a network drive. I have a query in this database that resets an autonumber field (jrnl_line_nbr) back to 1 when a criteria is met. I accomplish this by appending the jrnl_line_nbr field from another table that has one row with the Long Integer number 1 in it (Microsoft recommended method). It has been working flawlessly for over a year running on a Windows 98 PC. I now need to have this run from a Windows XP PC. Resetting AutoNumber no longer works. The appended row has the value 1 in it, but the following rows pick up with the la...

Sending Bulk emails
Is there a limit as to how many people you can send in 1 email. I have to send out information/news to 298 people at once, but I can only do so if I split the groups into 3 emails. -- S.a.s.t.news Check with your email account provider... most have a limit. Windows Mail doesn't. "sastnews" <sastnews@discussions.microsoft.com> wrote in message news:C8080B14-B5FF-4BF2-B892-32DE51260A4A@microsoft.com... > Is there a limit as to how many people you can send in 1 email. I have to > send out information/news to 298 people at once, but I can only do so...

Change drive letters on Exchange 2003 (first post hasn't appeared)
Hi, Sorry for the long post, trying to make sure I have everything Background: Windows 2003 Cluster Exchange 2003 Cluster, single;e virtual server in Active/Passive config (3 node cluster) Additional SRS server Exchange 5.5 server Single site, single Org When the Exchange cluster was installed (new resource created), the Data path was configured as M:\Exchsrvr (located on shared SAN as physical disk) The server is currently not live (only has test users, public folder replicas, not primary RUS) It has been decided that they wish to remove Exchange from the M: drive and change the dr...

Multiple user accounts not working properly
I am running Windows XP and have three user account logins. I was also running Outlook Express and set up three email accounts (each on their own login).. the first two email setups worked great. But the third one wasnt recognizing my password as valid. I Contacted the ISP. Walked through setup. Still didnt work. ISP instructed me to access mail via the internet. That worked. ISP said that proves the account is valid. Now, I just upgraded to Outlook 2003. Same problem with the third email user account. Funny thing is that this "3rd" email account is one of the tw...

Scroll box does not work
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I have a document that has scroll boxes sent to me, and I cannot get them to work, all I get is the picture. The odds are that ActiveX Controls were innappropriately used in the creation of the document. ActiveX isn't supported by the Mac OS. -- Regards |:>) Bob Jones [MVP] Office:Mac <RJ@officeformac.com> wrote in message news:59bae0d6.-1@webcrossing.JaKIaxP2ac0... > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power > PC I have a document that has scroll ...

working with caret
hi! is it possible to send data to the caret ? i mean, lets assume we have 100 editboxes, the user clicks on one of the them, i want programmaticaly be able to send a letter to the selected editbox without knowing wich one(editbox) it is. climax! wrote: > hi! > > is it possible to send data to the caret ? i mean, lets assume we have > 100 editboxes, the user clicks on one of the them, i want > programmaticaly be able to send a letter to the selected editbox > without knowing wich one(editbox) it is. > // From MSDN Q145616 CEdit* CMainFrame::IsEditInFocus() { TCHAR s...

Scanner not in Insert menu
I have created a new profile for a user on an XP machine, and the Scanner option no longer appears in the Insert menu when editing a new email. Is there an easy way to get this back? Thanks, is the scanner installed for the new user? is the account type (admin, power user, limited user etc) the same as for the old user? Some options will not be available to limited users - it depends on the scanner software. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http...

insert rows automatically
I have a spreadsheet where I need to copy a row and have EXCEL automatically copy new rows based on a number inputted by the user (less 1). I use a code which was previously posted that creates a box asking how many rows I need. This works fine but the problem is that people often input one row too many For example: If they need 10 rows overall , they inadvertently input a 10 ... giving a total of 11 rows (the original row + the 10 NEW rows) ) So I was wondering if anyone would know how to fine tune the code so that it would give me one less row than what the user inputs. Here's the cod...

inserting Hyperlinks
Call me CRAZY, but I cannot seem to insert a simple hyperlink into my Publisher document!! The Help file is very clear: Highlight the text in which you want to insert a hyperlink. On the Insert menu, click Hyperlink. Under Create a hyperlink to, click the type of hyperlink you want. Under Hyperlink information, specify an address or page When I look for 'Hyperlink' on the Insert menu, it is not there. Anyone know a fix?? I'm using Publisher 2000 SR-1 on a Windows 2000 PC. Any help would be much appreciated - THANKS! After managing to set up OE-QuoteFix on his new PC, Ed...

Excel 2002 SP3: In Open Dialog, Some File Names Appear in Blue
Can anyone tell me why some filenames appear in blue instead of black? I don't think I've ever seen this before today. Thanks! Hi Gloria The file is Compressed then -- Regards Ron de Bruin http://www.rondebruin.nl "Gloria" <Gloria@discussions.microsoft.com> wrote in message news:459C33F1-701E-4DD7-B009-8083B4DB0966@microsoft.com... > Can anyone tell me why some filenames appear in blue instead of black? I > don't think I've ever seen this before today. Thanks! Sorry to have bothered you with that one. I just found out that someone compres...

HOw Insert Table on Header and Footer (excel 2000)
Is there anyone who can help with my problem. I want to insert Table format on Header and footer...is this possible... it is a great pleasure if you can help me with this tnx... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Do you mean File|page setup|Header/Footer tab? In xl2002, you can insert pictures. Maybe you could save your table as a picture and insert that. (If you're using xl2k or before, maybe you can print data that will serve as headers/footers, ...

User cannot log in after Outlook 2003 installation
Hello, We are experiencing the same issues as others (http://www.mcse.ms/message1591947.html) but moving the mailbox does not clear up our issue: Users who receive the Outlook 2003 software cannot log into their mailboxes the next day...although OWA works just fine. Below have been my troubleshooting steps: 1. Had user log into her peer's workstation and create a new profile - same error. 2. User can access OWA just fine. 3. Move mailbox to new server - same error. 4. Removed Mailbox Rights and readded them. 5. Created pst, disconnected mailbox, created new mailbox on different ...

Problem with Excel: Simple multiplying calculations don't work.
When I apply simple mulitiplication formulas to a spreadsheet, the formula always returns "0" as an answer. If i select the formula field and hit enter, it then returns the correct result. I have all the updates. It doesn't seem to happen when I create a new worksheet, only when I'm doing it in someone elses spreadsheet. There is no protection applied. -Dave Hi Dave, Can you give examples? Formulas, values, etc. Is automatic calculation turned off? Tools>Options>Calculation tab -- Kind regards, Niek Otten Microsoft MVP - Excel "dforty3" <dforty...

Haveing problems inserting a row
Here is the problems I am working off of 3 different workbooks that are linked together Every time that I insert a new row, the forumlas do not update. I there a way for me to insert a new row so that it will change th forumlas?? I am hopping this could be done? thanks for any help you can give me Turb ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements On Thu, 4 Dec 2003 22:0...

The "Save As" Screen keeps appearing
While I am entering data into a cell, the "Save As" screen keeps appearing. Anybody else ever had this prblem? Could you by any chance be hitting the key combo CTRL+S which will bring up the save as dialog box. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- "Peggy" <pegacc...

Will it work ?
Is there a way to run Money Plus on Linux Speculation: in a VM running Windows? There's always GnuCash. "desgnr" wrote: > Is there a way to run Money Plus on Linux ...