Re: Trigger Problem

On 7 Oct 2004 11:20:43 -0700, "WebTracer" <randy_at_webtracersolutions.com> wrote:

>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.
>

Not quick and dirty, quick and messy.

>"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.
>

No. DDL commands are not allowed in PL/SQL, no matter how you create your PL/SQL. Actually it is a VERY BAD IDEA (no control over the database anymore) to have a trigger create any database object.

>"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 )?
>

That is why Oracle has manuals!' You can just look up execute immediate at the doc site http://tahiti.oracle.com In short
begin
execute immediate 'create view foo as select * from bar'; end;
/

>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.
The view is a database object. An object has an object id. Those are governed by a sequence. If you really run that trigger frequently you'll probably run out of sequence numbers. Apart form that: how can any DBA administer a database, in which objects are created on the fly!

--
Sybrand Bakker, Senior Oracle DBA