Avatar of 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:

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?

Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon