CODEWITHSUNDEEP
oracle-databaseplsql
Harsh Kairamkonda
Harsh Kairamkonda

Reputation: 397

Restrict creation of table on sunday in oracle

Question: create a pl/sql using a trigger to restrict creation of any table on Sunday

I tried

CREATE OR REPLACE TRIGGER sunday_trigger
BEFORE CREATE ON ALSPRD
  FOR EACH ROW
  DECLARE
  v_day DATE := TRUNC(SYSDATE);
  BEGIN
    IF TO_CHAR(v_day,'DY')IN ('SUN') THEN
      REVOKE create table from harsh;
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line('Error is:'||SQLERRM);
  END;

It creating error ORA-30506: system triggers cannot be based on tables or views

Upvotes: 3

Views: 143

Answers (2)

Littlefoot
Littlefoot

Reputation: 143013

I'd suggest a different approach.

Connected as SYS, create a trigger that prevents user (scott in my example) to create anything on desired day (I'll use Monday so that I could test whether it works as planned).

SQL> show user
USER is "SYS"
SQL>
SQL> create or replace trigger sunday_trigger
  2    before create on scott.schema
  3  begin
  4    if to_char(sysdate, 'DY', 'nls_date_language = english') = 'MON'
  5    then
  6       raise_application_error(-20000, 'You can not create any objects on Monday');
  7    end if;
  8  end;
  9  /

Trigger created.

Let's test it:

SQL> connect scott/tiger
Connected.
SQL> select to_char(sysdate, 'DY', 'nls_date_language = english') today from dual;

TODAY
------------
MON

SQL> create table so_test (id number);
create table so_test (id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: You can not create any objects on Monday
ORA-06512: at line 4


SQL> create or replace procedure p_test is
  2  begin
  3    null;
  4  end;
  5  /
create or replace procedure p_test is
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: You can not create any objects on Monday
ORA-06512: at line 4


SQL>

Let's pretend it is tomorrow (Tuesday):

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select sysdate from dual;

SYSDATE
--------
21.06.21

SQL> alter system set fixed_date = '22.06.21';

System altered.

SQL> connect scott/tiger
Connected.
SQL> select to_char(sysdate, 'DY', 'nls_date_language = english') today from dual;

TODAY
------------
TUE

SQL> create table so_test (id number);

Table created.

SQL> create or replace procedure p_test is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL>

Looks OK to me.

Upvotes: 3

Dmitry Demin
Dmitry Demin

Reputation: 2113

  1. Revoke user rights to create tables from harsh.
  2. Create another user <OWNER_PROC> with rights to create tables.
  3. Make a procedure <CREATE_TABLE_PROC> that creates tables and in this procedure you can make any logic.
  4. Grant execute on <OWNER_PROC>.<CREATE_TABLE_PROC> to harsh.

Upvotes: 1

Related Questions