Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3534
  • Last Modified:

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

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
zakwithu2012
Asked:
zakwithu2012
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
kamalranjanCommented:
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
 
lcohanDatabase AnalystCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
zakwithu2012Author Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
zakwithu2012Author Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
zakwithu2012Author Commented:
i resolved my issue using the computed column.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In which way does the accepted answer apply to "i resolved my issue using the computed column"?
0
 
zakwithu2012Author Commented:
i got the idea from one of the provided links.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Could you please post an example for the knowledgebase and hence to help others with similar issues?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now