Trigger Question
-
My new boss loves to use trigger to insert data into tables.
For example, there is a procedure to insert data from a text file A to Table B. He put a trigger in Table B, so the data inserts into table B, then the data will automatically insert into table C, put another trigger in table C to insert data into table D.
Then there is another text file X coming in to insert data to Table B.
He thought this way, he did not have to worry about insert data into table C and table D.
Unfortunately, text file A and text file B came from different sources, sometimes they come at the same time. Somehow it creates a deadlock on table C and table D. I don't know why. But he said it would. Is that true?
Also I want to know if it is a good practice to use trigger to insert data into multiple tables.
-
What's the full reasoning behind this?
Triggers are a great tool when used to do the right job. I'm not too sure in this case, but then again I don't have all the info.
-
Could go either way. Triggers are great for making sure no one does an end run on your data process, but if its just something being done from code I'd tend to just do in a proc wrapped with a good transaction.
-
His reason is once he received the data, he just needs to start the job to insert into one table, then the trigger will automatically update or insert into other tables.
I seldom use trigger in this way because it is hard to control the transaction. If something goes wrong, you have no idea which table get updated.
For me I rather write a procedure inserting into one table and make sure it is successful, then insert into another table.
Everyone does things different. The problem is he insists to use his way or NO WAY !!!!!
-
Heh... so write the triggers to send him personal pages and emails when the triggers fail
That will make him keenly aware of the problem he created 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree with Jeff. Also, if you can, create a circular trigger, where insert into Table A triggers insert into Table B which triggers insert into Table C which triggers insert into Table D which triggers insert into Table A. With an email for each trigger.
Regarding his argument about locking tables C and D, that will depend on whether (a) he wants the locking to occur and (b) how close to simultaneous the two originating transactions hit. That they could lock each other is very true. Whether they will relies on our favorite phrase here: 'it depends'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply