Re: Trigger Problem

"WebTracer" <randy_at_webtracersolutions.com> wrote in message news:1097173243.318760.265790_at_f14g2000cwb.googlegroups.com...
> Well, my job security notwithstanding, we have very little control over
> the database, triggers are one of the few things we can do to the db.
> This is also the only way that I could see to solve the problem - in a
> quick and dirty way.
>
> "Pl/sql doesn't allow DDL" - even if I am intentionally trying to stay
> away from that? Or does the fact the I let TOAD create outer shell of
> the trigger limit what I am trying to do? I tried to keep all commands
> to TRANSACT-SQL so that the PL part wouldn't come into play.
>
> "You would need to use EXECUTE IMMEDIATE " - again - coming from a MS
> SQL Server house, this is unfamiliar to me - as in 2 years of SQL
> programming against SQL Server 2000 I never once had to use it, or even
> be aware that it existed. Could you possibly provide some sort of
> example ( quick and dirty is fine )?
>
> On a separate note ... why is the view inside the trigger, when you are
> only pulling 2 or 3 rows of data, a bad idea? It simplified the query
> (and lines of code)required to get the job done immensely, and has
> almost no effect on the database. And the view will NEVER be very big,
> as it gets replaced every fire - and will always be small.
>

Why would someone want to create a view inside a trigger? Just use the SQL without creating the trigger or create the trigger outside of the trigger.(as part of the db) Views in Oracle are not evil as in some other RDBMSs they are. NDS (native dynamic SQL) or execute immediate is in the docs. I think it is in the pl/sql reference guide. Oracle and SQL Server are two very different beasts and trying to do the same thing in both in the same manner is highly frustrating.

Usually in triggers I call pl/sql packages unless the trigger is really really simple. That way I scale better. It might be better to know what problem you are trying to solve instead of just porting code. I think you will get better responces.
Jim Received on Thu Oct 07 2004 - 20:16:26 CDT