Re: Triggers - :new - pseudorecord

On Monday, September 16, 2013 11:57:10 AM UTC-7, jeremy wrote:

>
> Ideally was looking for a way to do "stuff" with dynamic SQL - know all
>
> about arrays and stuff, not sure how that article assists. The objective
>
> is, with as little programming as possible (or more accurately let's say
>
> modular / reusable), generate records into a "shadow" table which
>
> records previous versions of rows from TABLE_A into TABLE_A_SHADOW - we
>
> have to repeat this for a number of the most important tables in the
>
> system and provide online (web-based) access to super-users to query the
>
> shadow tables to determine who/when/what changed.
>
>
>
> Original thought was to do this using a bunch of dynamic SQL, generating
>
> XML represenatations of the changed records and storing these in a
>
> single table. Mladen was a little scathing of this approach (it being a
>
> relation databases, designed for tables & rows... the other approach
>
> could become very inefficient).
>
>
>
> Ultimately just trying to minimise the maintenance overhead when schemas
>
> change.
>
>
>
>
>
> --
>
> jeremy

Having dealt with "database blind" and "infinitely flexible schema" stuff in databases for far too long, I would run screaming from what you are trying to do, as minimizing the maintainability effort in that manner maximizes the maintainability effort in a more fundamental manner. But that probably just reflects the horribly bad way I've seen xml abused, as well as keeping referential integrity in the app and other such religious persecution.

But don't let me stop you. All I'm saying is that it works up to some medium level of complexity, then abruptly becomes a slow, unmaintainable mess. If you don't hit that inflection point, more power to you. Hey, sql was supposed to get rid of programming. Now it's a de facto syntactic interoperability standard. XML is supposed to fix that, by making everything orders of magnitude larger and more cumbersome...? It's like carrying around a bunch of crude oil and a cracking tower on top of your car, instead of just buying gas. So you don't have to pump gas.

jg

-- 
_at_home.com is bogus.
But of course, first you have to understand database performance, and how it differs from flat self describing data. http://www.infoq.com/news/2013/08/xml-json-performance