SQL Server Performance Forum – Threads Archive

INSERT INTO – TRIGGER

Hi, I have a table A and it has a FOR INSERT trigger.
Is there a way to fire trigger when I bulk insert data using INSERT INTO A SELECT * FROM B?
Thanks,
Gopal

This is one of those instances (people will hate me for this) where Oracle is nice to have over SQL Server. In Oracle, it is simple enough to just do a trigger for each insert at the row level. You have the option of row or statment level triggers or a combination there of. Because of row level triggers, the creators of the database system have to worry about what is known as mutating tables. Mutating tables deals with locks and inability to update certain fields because of them. You can google it for more info. Long story short, I believe Microsoft didn’t feel like dealing with the mutating table problem so they just didn’t offer that kind of trigger. The only way I know of to fix your problem is to use a cursor or loop and multiple inserts. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I would like to know if anyone else has found a workaround for this situation. John

OK here is an option for you also. I’m thinking you can use the inserted (conceptual) table and loop through those rows doing some action. That would allow you to use only one trigger and it should work on a bulk insert. Look in books online under the create trigger T-SQL command. There should be a description of the inserted and deleted tables that a trigger can use. John

Do you mean BULK INSERT or INSERT INTO? BULK INSERT has an option FIRE TRIGGER. May the Almighty God bless us all!
www.empoweredinformation.com

… and insert into will fire an insert trigger. I am afraid I don’t understand what is the problem.

Hi, Thanks for your inputs. I tried the solution provided by John. It works.
I am doing "INSERT INTO" not "BULK INSERT".
Now I am able to process one by one row.
I think it is not possible to avoid row by row processing within trigger using a cursor or while loop. Thanks,
Gopal

"I am doing "INSERT INTO" not "BULK INSERT". Not really sure what are you asking for since both INSERT INTO and BULK INSERT can fire triggers. "I think it is not possible to avoid row by row processing within trigger using a cursor or while loop." A cursor is processing one row at a time. The INSERTED special table in a trigger is a set based and should be taken advantage of. Again, not sure of what you want <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. I feel a terrible disturbance in the force… <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> May the Almighty God bless us all! www.empoweredinformation.com

]]>