× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Vinay,

I'd likely do something like this, which would be a great combination of
simplicity and good performance.

insert into TABLE_THAT_TRACKS_FAILURES
select CT.TABLE_NAME, UI.RESULT_SQL_CODE, { other failure input data }
from CONTROL_TABLE_CONTAINING_TABLE_NAMES CT
cross join table( UPDATE_OR_INSERT_EACH_TABLE( CT.TABLE_NAME, { pass other
data needed here for updates/inserts } ) )
as UI ( RESULT_SQL_CODE )
where UI.RESULT_SQL_CODE <> 0

Notes:
* UPDATE_OR_INSERT_EACH_TABLE is an SQL table function that takes your
table name as input, along with whatever other data you need to perform the
update / insert. It performs the update or insert, checks for an error,
and returns the SQL code resulting from the update or insert to the calling
result set.
* One REQUEST to the database updates all tables and tracks the failures.
* Granted, the SQL table function is performing multiple I/Os, but that is
happening at the database level with only data and SQL function code
involved. Carving out programs external to the database improves
performance. SQL function code runs very fast.
* I use SQL functions to do things like this all the time, and properly
implemented it runs really fast, with a tiny code volume.

Granted, your shop is resistant to SQL, so toss this out as a potential
solution but don't really sell it much. See if they bite. At least, they
should be given an opportunity to see what they are missing out on by being
resistant to SQL. They are making their job a lot harder than it needs to
be.

I might tinker with an SQL trigger solution as well, where only one table
is directly updated with a trigger on it, and the trigger's job would be to
propagate to the other tables (N - 1). That would also be a very small
code volume. I've not tried to use the code similar to what I posted above
inside a trigger, but it probably would work inside one. On the other hand,
updating the first table with one piece of code, and the other N - 1 tables
using a different piece of code, might deter me from tinkering with
involving a trigger, since now I have code in 2 places where I'd prefer 1.

SQL functions have some limitations. If they surfaced in this effort, I'd
push the functionality into SQL triggers and/or SQL stored procedures.

Mike

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.