Sql Server 2005
I have an after-insert trigger that grooms incoming data from PLC equipment from our plants.
Almost all of the time, one event's data received from this third-party system, equals a single record in our system.
But sometimes, I need to break a single record into multiple records on our side. So, let's say, I receive columnar data that looks like this:
TRANSACTION_TYPE VALUE_1 VALUE_2 VALUE_3
999 4 5 6
On these occasions, I want my trigger to do this: "Oh, this is a type 999 transaction. I now need to split the values I've received into separate records, instead of a single record".
What I'm doing, in an attempt to accomplish this, is to perform INSERTs from my after-insert trigger.
I like the way my logic looks, but I'll be d@mned if anything's happening. I've even try-catched my INSERT statements inside this trigger and logged the vlaue of ERROR_MESSAGE(). Nothing.
What's happening to the results of these extra INSERTs? Is this a terrible no-no? My logic is set up so that those "inner" inserts would *NOT* also cause the subsequent trigger-call to try to break this record into sub-parts again.
Any help is appreciated. If it's erroring, why no message? If it's not erroring, why no records?
Thanks.