In addition to the other posts:

The advantage of using a trigger in this case is that you don't have to
implement error handling. Any error except user defined errors and error 266
('Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing', but you're not likely to run into that)
will rollback the complete transaction, including the insert.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


[quoted text, click to view]

create a transaction for the whole process so that you can rollback all the
changes you made
in case an error occured. try to create insert trigger for Table A that will
perform desired task for
other tables.

--
gani




[quoted text, click to view]

[quoted text, click to view]
need to insert the data also into tables B, C, D, E, F, G & H. Then
based on the previous insert, table I & K have
to be populated as well. <<

The short answer is put all this work it into a single transaction.
Triggers are not portable, can be expensive and require a ton of
procedural code -- as you already can see in your nested procedures.

The fact that you have an ordering for the insertions implies that you
are writing procedural application code in SQL. Never do that; it
defeats the whole purpose of a non-procedural language.

This sounds insanely redundant -- the same data in 11 different
places!!?? If you are disassembling an input form (i.e. multiple kinds
of LOGICAL entities that come into the application in one PHYSICAL
package), do it in the front end instead.

I'd guess the RIGHT answer is to normalize the schema so that one fact
appears one way, one time and in one place. Then write your insertions.

Look up a series of articles by Tom Johnston series on redundancies that
span tables and all the problems they cause.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***