Solved

How to fire trigger when loading data into a table using BULK INSERT?

Posted on 2012-04-11
13
2,599 Views
Last Modified: 2012-04-16
I have the following trigger:

USE [CDR_CUCM7_1_5]
GO

CREATE TRIGGER [dbo].[trgAfterInsert_CDR_Record] ON [dbo].[CDR] 
AFTER INSERT
AS
BEGIN
DECLARE @Epoch_Date Datetime


	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
SET @Epoch_Date = (SELECT i.dateTimeOrigination FROM inserted i)
update [dbo].[CDR] 
set HumanDATETIME=dbo.fn_Epoch2HumanDate(@Epoch_Date);


END

Open in new window


Which supposed to to update the coulmn HumanDATETIME (in the same table) with the inserted value of coulmn 'dateTimeOrigination' after converting it from epoch format to human readable format using my function dbo.fn_Epoch2HumanDate.

bearing in mind that i'm using only Bulk Insert to fill the CDR table, the trigger doesn't fire. How can I modify it to work for multiple record inserts (BULK INSERT)?

thanks in advance
0
Comment
Question by:zakwithu2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 37832604
Firstly, your trigger is flawed - it only works for single row inserts, because of the
  SET @Epoch_Date = (SELECT i.dateTimeOrigination FROM inserted i)
line. You would have to change that anyway.

Secondly, do you really want to have the HumanDATETIME column set to the same value for ALL rows each time? Doesn't make much sense.

Thirdly, bulk insert does not allow for triggering at all. But you could change the trigger to an update trigger, and then issue an update on the CDR table (at least where the HumanDATETIME column is empty/NULL/whatever) after doing the Bulk Insert. On the other hand - you could call the correct update after Bulk Insert anyway.
0
 
LVL 2

Expert Comment

by:kamalranjan
ID: 37832713
Hi,

It seems your code is not handling multiple records. See the line below which needs to be changed to get the output in a cursor instead of a variable:

SET @Epoch_Date = (SELECT i.dateTimeOrigination FROM inserted i)

And then process each record in that cursor.

Refer the links below for more info:

http://stackoverflow.com/questions/7983352/sql-server-trigger-not-executing-for-multiple-rows

http://stackoverflow.com/questions/407935/how-to-test-for-multiple-row-actions-in-a-sql-server-trigger

http://stackoverflow.com/questions/7258748/after-insert-trigger-looping

http://stackoverflow.com/questions/662010/handling-multiple-records-in-a-ms-sql-trigger


Kamal
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 37833264
Please see Solution from answer below for similar issue with one comment - if your BULK INSERT data set is huge you should expect to have a performance issue with the target table and if it is a online heavily used table you should think what the trigger does and maybe take a different approach like do it in a SQL job after the BULK INSERT instead of a AFTER INSERT trigger:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27635554.html
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 70

Expert Comment

by:Qlemo
ID: 37833444
kamalranjan &  lcohan,

Please only post if you have to add something to the thread. My comment includes both of yours. And further, just providing a bunch of links does not help usually.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 37833473
I have been inprecise in one regard: Bulk operations usually disable triggers, but you can override that by supplying the switch FIRE_TRIGGERS in BULK INSERT (for details see http://msdn.microsoft.com/en-us/library/ms187640.aspx). You will still have to fix the "single-row" mentioned above.
0
 

Author Comment

by:zakwithu2012
ID: 37834134
Thank you ALL Qlemo, kamalranjan and  lcohan...

Yes, I forgot to copy the WHERE part of the update query. It should be like this:

....
update [dbo].[CDR]
set HumanDATETIME=dbo.fn_Epoch2HumanDate(@Epoch_Date)
where dateTimeOrigination =@Epoch_Date ;
....

let me explain my main requirement you might find different approach rather than using triggers:

CDR table (98 Columns) is a very large table growing by about 70000 records/day ... data is being loaded from flat text files using bulk insert with file format
more details about this CDR table i posted here
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27640962.html

my problem now is there is one field 'dateTimeOrigination' comes with epoch date format which undesired for me. i created new column HumanDATETIME to save the converted datetime in it.Simply i want to convert the epoch date ON THE FLY while inserting and save it in the new HumanDATETIME column.
i create this user function which take care of converting from epoch to human format and it works fine (no need to spend effort on this).
Is there any way to achieve this?

Another approach which i tried to achieve this by setting default value in HumanDATETIME but it didn't work and i want your comment please on this:

I can normally put default value in  like getdate() any datetime fields and it works fine when inserting.
i did the same for my case ... i put the default value of the field 'HumanDATETIME' to be
dbo.fn_Epoch2HumanDate(dateTimeOrigination )

Open in new window


it didn't work.
can anyone tell me is it possible to play on default value as above and what is the required  correction to make it work?


thank you again
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 37834255
The default should work, as BULK INSERT honours that. But it requires that the data field in the source file is unoccupied or missing.
However, instead of using a trigger or default value, I recommend to perform:
   bulk insert ...
   update [dbo].[CDR] set HumanDATETIME=dbo.fn_Epoch2HumanDate(dateTimeOrigination)
where HumanDATETIME is null @Epoch_Date;

Open in new window

HumanDATETIME needs to be NULL for new rows to get it working.
0
 

Author Comment

by:zakwithu2012
ID: 37836141
Hi
I didn't understand this:
where HumanDATETIME is null @Epoch_Date;

the part "where HumanDATETIME is null " is ok but this part "@Epoch_Date;" is not.
can you elaborate please.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 37837017
Typo, sorry. The line ends with the "is null", the remainder is wrong:
   bulk insert ...
   update [dbo].[CDR] set HumanDATETIME=dbo.fn_Epoch2HumanDate(dateTimeOrigination)
where HumanDATETIME is null;

Open in new window

0
 

Author Comment

by:zakwithu2012
ID: 37844969
i resolved my issue using the computed column.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 37846304
In which way does the accepted answer apply to "i resolved my issue using the computed column"?
0
 

Author Comment

by:zakwithu2012
ID: 37849988
i got the idea from one of the provided links.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 37850567
Could you please post an example for the knowledgebase and hence to help others with similar issues?
0

Featured Post

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question