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 to be edited by the person responsible. Or in other words make an insertion into AUDIT_TBL1 where updated tbl1 id equals id in table 1
I've been stuck here for a very long time and would really appreciate any help:)