Bulk Insert? What the Trigger Status ?

One of my friends told me that the trigger is not fire properly when u inserts data at time or delete multiple records without where conditions.

As my thought, it is actually rubbish statement. The behavior of a trigger (that it fires or not) depends on how the data in inserted or deleted or updated.

Lat took the example of Insert statement and illustrate the process of bulk insert statement.

 Bulk Insert in done by three processes:

1.    The statement like this

 INSERT INTO TargetTable

SELECT * FROM SourceTable

 

In this statement the trigger is enabled and fire automatically.

 

2.    The BCP

By default the Trigger status of destination table is disabled.

 

3.    The BULK INSERT

By default the trigger status of the destination table is disabled.

 

In the case of BCP and BULK INSERT we can explicitly fire the triggers. For BCP we can use                -h "FIRE_TRIGGER" qualifier the mentioned the trigger name to fire.

In the BULK INSERT we can use "FIRE_TRIGGER" qualifier to run the mentioned trigger as parameters.

In case of Bulk insert the triggers fire when the bulk insert process is completed or the batch process is completed.

I hope that the information mentioned here is quite informative and helps a lot to build concepts.

 

Posted by: MR. JOYDEEP DAS