Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to run DDL in a Trigger?

420083May 6 2011 — edited May 9 2011
I am trying to create a trigger that automatically sets the quota for any new users created in the database. However, I am running into the follow error because whenever you run DDL, Oracle automatically commits, which you can't do in a trigger. Does anyone have any ideas on how to get around this? We are on Oracle 11gR1.

. The problem is, I am getting an ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger

CREATE OR REPLACE TRIGGER TESTER.AUTO_QUOTA
AFTER INSERT ON USERS FOR EACH ROW
DECLARE
sqlstmt VARCHAR2(100);
BEGIN
sqlstmt:='ALTER USER ' || :NEW.USERNAME || ' QUOTA UNLIMITED ON ' || :NEW.TABLESPACE_NAME;
execute IMMEDIATE SQLSTMT;
END;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 6 2011
Added on May 6 2011
8 comments
957 views