As can be seen, I have tried both options of execute immediate as well as dbms_sql. For execute immediate, I get the error message 'Invalid option' and for dbms_sql I get the error message 'cannot commit inside trigger' These are runtime errors, the trigger compiles fine
Creating users and granting privileges are data definition language (DDL) commands, which perform implicit commits. As you might know, using COMMIT or ROLLBACK is not allowed in triggers. To overcome this restriction you might want to use autonomous transactions (new in 8i). Check the Oracle documentation or some newer PL/SQL book for the details about the use of utonomous transactions.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
put all these stuff in a packaged procedure or a procedure with a Pragma autonomous_transaction and call the procedure in ur trigger. this works and for further ref see autonomous transactions
Thanks a lot for your inputs. Its working great with the autonomous transactions, however, its giving an error "insufficient privileges'. The user executing this trigger has dba role. Why do you think this might be happenign ?