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
zakwithu2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.