Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Create after update/insert trigger, insert only for corresponding row

User9999May 3 2021

Hello community:)
 I am in the process of implementing a trigger, which is triggered as soon as a change occurs in table 1. The first user in this case is a student, who can edit this table using a form. The trigger then records in a so-called AUDIT table who has done what and when in the form intended for them. However, I have the following problems:
A person can be selected as responsible for several forms
However, if this person edits the table or the form intended for him, the action carried out should only be recorded for this form. At the moment, however, the trigger looks to see in which forms this person is entered as the responsible person and logs it for all the rows it finds.

create or replace TRIGGER TRG_AUDIT_TBL1
  -- starts on every update or insert command
  AFTER INSERT OR UPDATE ON TBL1
  FOR EACH ROW
  DECLARE
  v_user varchar2(30);
  v_userid USERS.UUID%TYPE;
  v_done_action varchar2(50);
  v_stud_id STUDENTS.ID%TYPE;
  v_resp RESPONSIBLE.ID%TYPE;


  v_form_id form.id%TYPE;
  does_exist number;


  BEGIN
  v_user := SYS_CONTEXT('APEX$SESSION','APP_USER');
  select UUID into v_userid from users where lower(username)=lower(v_user);

  select count(*) into does_exist
  from STUDENTS
  where uuid in (select UUID from users
  where uuid=v_userid);

  if (does_exist>0)
  then
  select STUID into v_stud_id from students where uuid = v_userid;
  end if;

  select count(*) into does_exist
  from respsonsible
  where uuid in (select UUID from users
  where uuid=v_userid);

  if (does_exist>0)
  then
  select r.ID, f.ID into v_resp,v_form_id from RESPONSIBLE r left outer join forms f on r.respid=i.ID
  where r.uuid = v_userid ;
  end if;



  IF :NEW.STATE ='REQUESTED' then
  v_done_action :='test123';

  ELSIF :NEW.STATE ='APPROVED_BY_RESP' THEN
  v_done_action :='test1234.';

  ELSIF :NEW.STATE ='DENIED_BY_RESP' THEN
  v_done_action :='test12345.';

  ELSIF :NEW.STATE ='CHANGE_REQUEST_BY_RESP' THEN
  v_done_action :='test123456.';

  END IF; 

  IF ( v_stud_id = :NEW.STUID) or
  ( v_resp=:NEW.RESPID) and
  (:OLD.STATE <>:NEW.STATE) then


  INSERT INTO AUDIT_TBL1
  (.............)
  VALUES
  (....................);
  END IF;

  END;

So I wonder if it is somehow possible to say that an insert on the AUDIT_TBL1 table should only happen for the row in which the ID of table 1 is the same as the ID that is edited by the person responsible. Or in other words make an insertion into AUDIT_TBL1 where updated/edited tbl1 id equals id in table 1.

I've been stuck here for a very long time and would really appreciate any help:)

Comments
Post Details
Added on May 3 2021
2 comments
714 views