How to run DDL in a Trigger?
420083May 6 2011 — edited May 9 2011I 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;
/