Avatar of bamapie
bamapie

asked on 

INSERTing during an INSERT trigger

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.
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
bamapie

8/22/2022 - Mon