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