Re: [Andro-general] MS SQL Server

[Justin D. <zip...@gm...>]

Ken,

How exactly are you doing these replacements with INSTEAD OF triggers?
Are you using views? Unless I am missing the IF TRIGGER_NESTLEVEL() >
1 THEN DONT_INSTEAD_OF() syntax, I think its impossible to do without
views.

In my particular case, a view won't work. I'm using a trigger to
populate a new column from an attribute in an xml column because we
don't have time to change and test all the nasty stored procs used by
our ETL process. To illustrate a example:


Id | XmlColumn                        | NewColumn
1  | <Element Attrib1="blarg"/>  | blarg

While you can do wonderful things with xml columns in SQL server
200(5|8), the indexes are broken in such a way that joins are really
slow. Hence the quasi denormalization (for lack of a better term due
to the unholy matrimony of SQL and XML) you see above.

Now the trigger is just a work around, at some point we will update
and test all the stored procedures to populate that piece of data in
the column and not the attribute. Since this is a temporary trigger to
avoid changing code, introducing a view into the mix is not an option.
However, if I can make an INSTEAD OF trigger work, I'd like to compare
its performance to the AFTER trigger I now use.


On Sat, May 2, 2009 at 9:46 AM, Kenneth Downs <ke...@se...> wrote:
>
> Well I had to get my head out of postgres mode.  SQL Server does allow
> replacements pretty elegantly, but the approach is vastly different.
> It's actually easy.
>
> The idea is you create an INSTEAD OF trigger.  In the case of an INSERT
> trigger, you actually have to code an INSERT that puts in the mix of
> what the user typed and what you are going to replace.  If you don't do
> that, nothing happens.
>
> Conceptually this is easy to handle for all three operations, INSERT,
> UPDATE, DELETE.  The next question becomes how much work is involved?
> Can Andromeda's trigger generation system be generalized to allow for
> this other way of thinking, or will it work better if everything is
> hardcoded for each platform?
>
> Here is the worst case:
>
> 1) Column A is a FETCHed value
> 2) Column A is also a foreign key to some other table, and must be checked
> 3) Column B is a FETCHED value, based on Column A
> 4) Column C is calculated from A and B
>
> That's the hairiest situation Andromeda handles, and it does it rather
> well.  There is a complex column dependency system that works out the
> order of calculations and FK checks to make sure those go well.
>
> So, can it be generalized to all platforms?
>
> Well, in a row-by-row situation, yes.  Each platform has a slightly
> different syntax, but basically the trigger can generate all derived
> values and store them in variables (that's what it does now).  Then, if
> its SQL Server, it must actually issue an INSERT or UPDATE to commit the
> values.
>
> But I still have to experiment and work out if MS SQL triggers are
> row-by-row or statement.  I seem to recall they are statement level by
> default, and so we get into a bit of a jam.  Have to conduct an
> experiment there.
>
> More later...
>
> --
> Kenneth Downs
> Secure Data Software
> ke...@se...  www.andromeda-project.org www.secdat.com
> Office: 631-689-7200
> Cell: 631-379-0010
> Fax: 631-689-0527
>
>
> ------------------------------------------------------------------------------
> Register Now & Save for Velocity, the Web Performance & Operations
> Conference from O'Reilly Media. Velocity features a full day of
> expert-led, hands-on workshops and two days of sessions from industry
> leaders in dedicated Performance & Operations tracks. Use code vel09scf
> and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf
> _______________________________________________
> Andro-general mailing list
> And...@li...
> https://lists.sourceforge.net/lists/listinfo/andro-general