January 28, 2009 at 4:29 pm
[font="Verdana"]This is where you hit the limitations of my memory. I know there is a way to do it (i.e. get access to the data that has changed), because I remember doing it. I just can't remember how. So you're best hitting the Oracle forums. 😀
Best of luck![/font]
January 28, 2009 at 5:35 pm
bruce.cassidy (1/28/2009)
[font="Verdana"]The Oracle create trigger syntax is as follows:[/font][font="Courier New"]
CREATE [OR REPLACE] TRIGGER
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON
[REFERENCING [NEW AS ]]
[FOR EACH ROW [WHEN ( )]]
[/font]
[font="Verdana"]If you leave off the "for each row" bit, it's a set-based trigger.
Other than that, hit the Oracle forums. :)[/font]
Ummm, I'm pretty sure that if you leave off the "for each row", it becomes a single row trigger... not set based at all.
--Jeff Moden
January 28, 2009 at 5:37 pm
Jeff Moden (1/28/2009)
bruce.cassidy (1/28/2009)
Ummm, I'm pretty sure that if you leave off the "for each row", it becomes a single row trigger... not set based at all.
[font="Verdana"]See limitations of my memory above. I haven't touched any Oracle in over a year now, so it's quite possible I have it backwards.[/font]
January 28, 2009 at 6:24 pm
Don't know if it's appropriate, but some of the Oracle ETL processes I've come across use a staging table for references to old. Maybe things have moved on since 8i. http://www.oracle-base.com/articles/8i/BulkBinds8i.php
Max
January 29, 2009 at 2:21 pm
[font="Verdana"]Personally, I'd be a little scared about an ETL process that relies on triggers.
In my past experience, the best thing I have found when designing systems that use triggers is to try and redesign so as to reduce the use of triggers. I think the last system I designed for Oracle only used triggers to populate ID fields, and only if they weren't already populated (so a bulk load process could preload the ID field, and the trigger wouldn't even fire.)
The problem with magical code that happens in the background is that it's too easy to forget and it upsets things like transaction lifetimes and so on. The worst I ran into was a system (in SQL Server!) that had an extreme case where it fired off so much trigger logic (trigger logic that stepped down to RBAR) that the original transaction post could time out.
Better to be up-front about business logic, I have found. It makes it easier to pull logic out of the database and host it in a business logic layer on an application server where appropriate. Burying business logic (such as data transformations within an ETL process) within triggers just ends up creating maintenance and performance issues.
Well, that's my experience. I'd be interested to hear from a fan of triggers. 😀
[/font]
January 29, 2009 at 2:37 pm
I like triggers. They make firearms much more user friendly than keyboards and mice would. 🙂
In my book, triggers have one good use, which is enforcing code at a level so low that nothing can bypass it on accident, similar to the way constraints and data types on columns work. When that's necessary, they are a good thing. When it's not, they are usually more of a problem than they are worth.
On the other hand, both of the triggers that I built last year were to bypass the fact that the devs who built the business logic layer weren't competent to build the code I needed, and there were pieces of code directly working on the tables without going through procs. Didn't have any choice except triggers in those cases. But incompetent devs as a reason for anything should, one hopes, be the exception, not the rule.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 2:40 pm
GSquared (1/29/2009)
I like triggers. They make firearms much more user friendly than keyboards and mice would. 🙂
Heh... firearms... the original point and click device 😛
But incompetent devs as a reason for anything should, one hopes, be the exception, not the rule.
Should have "pulled the trigger" on them. You'd have gotten a "kick" out of it :hehe:
--Jeff Moden
January 29, 2009 at 2:41 pm
[font="Verdana"]I guess we are verging from the original topic.
I've always found security is a better mechanism for enforcing access to database objects. Oh, there's a feature I like in Oracle that still isn't in SQL Server: row level security!
[/font]
January 29, 2009 at 2:48 pm
Bruce W Cassidy (1/29/2009)
[font="Verdana"]I guess we are verging from the original topic.I've always found security is a better mechanism for enforcing access to database objects. Oh, there's a feature I like in Oracle that still isn't in SQL Server: row level security!
[/font]
Row level security would be good. Can be emulated in SQL Server, by using the right Where clauses, etc., but it does have its limitations that way.
On the access I was dealing with, denying direct access to the tables would have required rewriting applications. (I came in after all these were already written and in use.) The people who would have had to rewrite them were the same ones who couldn't figure out how to get an update in a details table to update a column in a master table (which, truth be told, should never have been there in the first place; should have been aggregated in queries instead of being stored in a table). It's a tale of woe.
On the original subject, I haven't used Oracle or DB 2. Tried MySQL nearly a decade ago and couldn't get the hang of it (was very new to databases back then). The two I've used have been Access and SQL Server. There's no real comparison between those two, especially with Express Edition available for free. So, all I can say is, I haven't run into a database problem yet that I couldn't solve in SQL Server. It has been more than adequate to the needs of the businesses that have been using it. In that, Oracle is certainly capable of filling the same needs, but at a higher cost. To that extent, I have to say SQL Server is better, for those businesses. Not better by any objective standard, but better for those businesses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 3:19 pm
GSquared (1/29/2009)
I haven't used Oracle or DB 2. Tried MySQL nearly a decade ago and couldn't get the hang of it (was very new to databases back then). The two I've used have been Access and SQL Server. There's no real comparison between those two, especially with Express Edition available for free. So, all I can say is, I haven't run into a database problem yet that I couldn't solve in SQL Server. It has been more than adequate to the needs of the businesses that have been using it. In that, Oracle is certainly capable of filling the same needs, but at a higher cost. To that extent, I have to say SQL Server is better, for those businesses. Not better by any objective standard, but better for those businesses.
[font="Verdana"]Agreed. One of the primary reasons I recommend Oracle over SQL Server is because an organisation already has Oracle, in which case they've already paid the higher price and they may as well get the most out of their investment. For most organisations, it pretty much doesn't matter whether they pick Oracle, SQL Server or DB2. They'll all do the job.
You haven't missed much with MySQL. For those of us used to a modern RDBMS, it's like a giant leap backwards. I far prefer PostGress or Firebird.[/font]
January 30, 2009 at 7:33 am
And that statement right there is why I wonder about the people who are so hooked on the idea of code portability. In most cases, the gain from switching database vendors is much smaller than the cost of doing so.
If you're building an application that might sit on top of many types of database servers, that's one thing. If you're building an internal application that employees use, it's highly unlikely that it will ever be migrated; and if it is, it'll almost certainly be part of a major evolution anyway, in which recoding for a different RDBMS is the least of your worries.
The potential ROI on database agnostic database code has never struck me as being higher than the known ROI of using certain pieces of proprietary code. Since that's the only valid business decision model that I know of, I don't get the argument the other way.
In summary, I definitely agree that "the better RDBMS (Oracle, SQL Server, DB2, et al)" is the one that you already have skills available for. Otherwise, it's pretty much going to be based on price and needed features.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2009 at 8:17 am
GSquared (1/30/2009)
If you're building an application that might sit on top of many types of database servers, that's one thing. If you're building an internal application that employees use, it's highly unlikely that it will ever be migrated; and if it is, it'll almost certainly be part of a major evolution anyway, in which recoding for a different RDBMS is the least of your worries.
Actually many large corps (perhaps even most of them) have more than one DBMS from different vendors. For example they may have Oracle or DB2 for certain enterprise apps and some MS-SQL or MySQL for tactical solutions or workgroup servers. They may well have medium and long term strategies that involve switching applications between DBMS platforms. So for those organisations portability is a factor even if they don't plan major evolutions that move everything wholesale from one DBMS to another.
January 30, 2009 at 12:24 pm
Personally, I think we should scour the earth of all rdbms and use an abacus, ok, at a push flat files, but there my generosity ends.
Max
February 1, 2009 at 1:27 pm
Max (1/30/2009)
Personally, I think we should scour the earth of all rdbms and use an abacus, ok, at a push flat files, but there my generosity ends.
[font="Verdana"]We could run our business on flat files! Lots and lots of flat files! Perhaps with some sort of "self describing" ability. Then we could invent a piece of software that pushes all of those flat files around, merges them, spits out new ones, fires off random messages (as more flat files). We could have our flat files talking to each other! Wouldn't that be great for business?
Hmmm. But what would we call that sort of software? "Something that allows us to run our business on talking with flat files" seems a little long-winded. BizTalk for short? :P[/font]
February 1, 2009 at 1:33 pm
GSquared (1/30/2009)
And that statement right there is why I wonder about the people who are so hooked on the idea of code portability. In most cases, the gain from switching database vendors is much smaller than the cost of doing so.If you're building an application that might sit on top of many types of database servers, that's one thing. If you're building an internal application that employees use, it's highly unlikely that it will ever be migrated; and if it is, it'll almost certainly be part of a major evolution anyway, in which recoding for a different RDBMS is the least of your worries.
The potential ROI on database agnostic database code has never struck me as being higher than the known ROI of using certain pieces of proprietary code. Since that's the only valid business decision model that I know of, I don't get the argument the other way.
[font="Verdana"]Perhaps a whole 'nother discussion there.
There are people out there who are not motivated by ROI: I generally refer to these people as "purists". That is distinct from the set of people who I refer to as "pragmatists", and who acknowledge that real world issues such as existing infrastructure, current architecture, project constraints and politics all have their place in defining a solution.
But purists aren't valueless: they do make us think. Often, it's to come up with why we may disagree with them.
Anyway, in general I would agree with you. There's no point bending over backwards to create "platform independent code" when it's of no actual value.
It's interesting though that two of the most popular programming platforms (Java, .Net) are, at the core, attempts to create platform independent code. So perhaps there's something in that?
[/font]
Viewing 15 posts - 76 through 90 (of 250 total)
You must be logged in to reply to this topic. Login to reply