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
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.