CODEWITHSUNDEEP
oracle-databasetriggersoracle11g
Norman Wei
Norman Wei

Reputation: 51

Create Statement in a Trigger Oracle 11

Still pretty new to Oracle 11g and RDMBS (in general)

I want to add a trigger which would create a user when a row was inserted into a table.

I encountered the default error saying that I can not put a create statement in the body of the trigger (PLS-00103). I tried different positions of the line in the body as well as isolated the line completely in a trigger but the error always occurs at the "CREATE" position. Does anyone know if this is a restriction on triggers/stored procedures? If so, what would be the workaround for this sort of restriction?

I can imagine having a trigger which creates something can be extremely dangerous to the database but I am not experienced enough to know for sure.

edit reasoning for adding such a trigger:

The reason I want to add such a trigger is because

First, I want to initialize an employee table.

Second, with no employees in the table, each added employee will (1) create a username and pass for them to use and (2) provide them the privileges of an employee_role

edit added code

CREATE VIEW EMPLOYEES_VIEW AS
SELECT * FROM EMPLOYEES_TABLE;

CREATE TRIGGER NEW_EMPLOYEE_ROLE
INSTEAD OF INSERT
ON EMPLOYEES_VIEW
DECLARE INSERTED_EMPID CHAR;
BEGIN
    INSERTED_EMPID:= :NEW.EMPID;

    INSERT INTO EMPLOYEES_TABLE
    (EMPID, NAME, TAXID, COUNTRY, HIREDATE, BIRTHDATE, SALARY, BONUS, DEPTID, ADDRESS, ADDRESS_CITY, ADDRESS_ZIP, ADDRESS_COUNTRY)
    VALUES
    (:NEW.EMPID, :NEW.NAME, :NEW.TAXID, :NEW.COUNTRY, :NEW.HIREDATE, :NEW.BIRTHDATE, :NEW.SALARY, :NEW.BONUS, :NEW.DEPTID, :NEW.ADDRESS, :NEW.ADDRESS_CITY, :NEW.ADDRESS_ZIP, :NEW.ADDRESS_COUNTRY);

    CREATE USER INSERTED_EMPID IDENTIFIED BY INSERTED_EMPID;
    GRANT EMPLOYEE_ROLE TO INSERTED_EMPID;
END;
/

Thank you

Upvotes: 0

Views: 102

Answers (1)

Alex Poole
Alex Poole

Reputation: 191580

You can't run any DDL from any PL/SQL context, which includes a trigger, unless you use dynamic SQL; so create ... isn't valid as static SQL in a PL/SQL block.

But there are further restrictions on triggers. Because they fire during a DML statement, you can't commit or rollback from within one, and DDL implicitly commits. It's possible to get around that but it isn't a good idea.

Assuming you really need to create a user at the same time you insert a row it would be simpler to use a procedure to do the insert and dynamically create the user, and then ensure rows are only inserted by calling the procedure.

create or replace procedure add_user(p_user_id varchar2, ...) as
begin
  insert into your_table ... ;
  execute immediate 'create user ' || p_user_id
    || ' identified by ...';
end;
/

You still need to still be aware that the insert will be implicitly comitted even if the create fails. It would be a good idea to check the user ID is a valid identifier, and any other passed parameters that form part of the create statement are also valid, before you insert to minimise that risk.

Upvotes: 4

Related Questions