CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle11gtriggers
Zeeshan Khan
Zeeshan Khan

Reputation: 1

Trying to insert into table after rising the application error but its rollbacks

I am trying to make a Trigger to restrict the user to performing DDL on Saturday and Sunday but if someone tries to insert data it will save that that in the weekend_action table but also raise application error that cannot perform DDL.

SQL QUERY:

create or replace trigger tgr_wkd_action
before insert
on tbl_39_dept_k 
for each row
declare
begin
  IF trim(TO_CHAR(sysdate,'Day')) IN ('Tuesday', 'Sunday') then 
     RAISE_APPLICATION_ERROR (-20000,'you cannot perform DDL on Weekend');
  end if;
  
  if inserting then
     insert into user_admin.weekend_actions values 
       (:NEW.Dept_no,
        'updation',
        'user'||user||'trying to insert data on'||'_'||sysdate||'from Table tbl_39_dept_k');
  end if;
end tgr_wkd_action;

Upvotes: 0

Views: 85

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

if someone tries to insert data

Exactly. Insert.

Your trigger fires before update, and that's a different DML.

Apart from that:

  • inserts and updates are DML (data manipulation). DDL you mentioned is data definition (these are create table, alter table, ...)
  • weekend action vs. Tuesday? Since when is Tuesday weekend?

Perhaps you meant

SQL> CREATE OR REPLACE TRIGGER tgr_wkd_action
  2     BEFORE INSERT OR UPDATE
  3     ON tbl_39_dept_k
  4     FOR EACH ROW
  5  DECLARE
  6  BEGIN
  7     IF TRIM (TO_CHAR (SYSDATE, 'Day', 'nls_date_language = english')) IN
  8           ('Saturday', 'Sunday')
  9     THEN
 10        RAISE_APPLICATION_ERROR (-20000, 'you cannot perform DML on Weekend');
 11     END IF;
 12
 13     IF INSERTING
 14     THEN
 15        INSERT INTO weekend_actions
 16                VALUES (
 17                          :NEW.Dept_no,
 18                          'inserting',
 19                             'user'
 20                          || USER
 21                          || 'trying to insert data on'
 22                          || '_'
 23                          || SYSDATE
 24                          || 'from Table tbl_39_dept_k');
 25     ELSIF UPDATING
 26     THEN
 27        INSERT INTO weekend_actions
 28                VALUES (
 29                          :NEW.Dept_no,
 30                          'updating',
 31                             'user'
 32                          || USER
 33                          || 'trying to update data on'
 34                          || '_'
 35                          || SYSDATE
 36                          || 'from Table tbl_39_dept_k');
 37     END IF;
 38  END tgr_wkd_action;
 39  /

Trigger created.

SQL>
SQL> INSERT INTO tbl_39_dept_k (dept_no) VALUES (10);

1 row created.

SQL>

Tables' contents:

SQL> select * from tbl_39_dept_k;

   DEPT_NO
----------
        10

SQL> select * from weekend_actions;

   DEPT_NO ACTION     MSG
---------- ---------- ----------------------------------------------------------------------
        10 inserting  userSCOTTtrying to insert data on_23.11.21from Table tbl_39_dept_k

SQL>

Pretending it is weekend today (while it is Tuesday):

<snip>
  7     IF TRIM (TO_CHAR (SYSDATE, 'Day', 'nls_date_language = english')) IN
  8           ('Tuesday', 'Sunday')
  9     THEN
<snip>
 39  /

Trigger created.

SQL>
SQL> INSERT INTO tbl_39_dept_k (dept_no) VALUES (20);
INSERT INTO tbl_39_dept_k (dept_no) VALUES (20)
            *
ERROR at line 1:
ORA-20000: you cannot perform DML on Weekend
ORA-06512: at "SCOTT.TGR_WKD_ACTION", line 6
ORA-04088: error during execution of trigger 'SCOTT.TGR_WKD_ACTION'


SQL>

Upvotes: 2

Related Questions