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:)