Solved

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

Posted on 2012-04-11
13
2,388 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
13 Comments
 
LVL 69

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 39

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

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 69

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 69

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 69

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 69

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 69

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

808 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