If you don't prefer to put program code in SP, why do you want to create them on the fly? You can do all in the middle-tier with ease that you can do using SP. Database languages such as T-SQL,PL-SQL are not as rich as .NET framework. In your case, you will have to learn both PL/SQL, T-SQL and that is a lot of work. If you do this in middle tier, you can have central place for all your logic for Oracle/SQL Server both.

You can create SP on the fly. It is just a SQL statement that goes through. As you presumed, you will have access issues, multi-user conflicts.

Triggers are faster but if you do not process millions of transaction in a short period, there is no need for speed. Have you done any proof of concept on speed?

>This may sound like a crazy question, but here goes...
>
>Let's say I have an application that will need to work with either Oracle or SQL Server. For reasons too lengthy to discuss [a horrible oversimplified perception of portability], program code cannot be placed in the database. All query access, inserts, etc. must be done through the middle-tier.
>
>There is a good amount of logic I'd like to be able to incorporate into update triggers when our main production tables are updated. Again, I am being 'blocked' from writing update trigger code in the database. I've been told it must reside in the middle-tier.
>
>Now for the nutty questions...
>
>1) I swear that some time ago I've seen source code that 'builds' the code for an update trigger 'on the fly' in the middle tier. Can this be done in the middle-tier?
>
>2) If the answer would amazingly be yes, my next question...would the connection to the database need DBO-level access in order to create that update trigger code?
>
>3) Also, if the answer is yes, obviously the trigger needs to have a name. Suppose two users are updating at the same time. If a trigger is being created 'twice' with the same name, would that cause a conflict?
>
>(I realize that even if this is possible, the syntax for Oracle and SQL Server is somewhat different...I can account for that in code).
>
>I'm sure these are questions to laugh at, but I'm kind of up against the wall. We have application logging and other requirements when data is saved...doing so in triggers seems to be cleaner and faster than adding logic (and additional INSERT statements) in the middle-tier.
>
>Any thoughts? Thanks...
>Kevin