Hello Sir, 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, ...
Discy Latest Questions
Hello Sir, I have 3 types of users. These perform different actions in a process. Depending on the action, I want to record the pages in a seperate table (table2) (STATE=REQUESTED, PAGE=80,TBL1ID=1). Depending on user logging in, he can read only edit or read only some fields. I created a trigger which creates a new row in the table (table2) when an insertion is made in the other table(table1). However, as soon as an update is made to this table(table1), the row of the other table (table2) should be updated, not whole row columns of the table. So I would like to create a trigger which updates a row depending on value of column of the other table(table1). I tried something like this. create or replace TRIGGER INSERT_PAGE BEFORE update or insert on TABLE1 for each ROW begin if INSERTING THEN INSERT INTO TABLE2 (TBL1ID,STEP,PAGE) VALUES (:NEW.TBL1ID,:NEW.STEP,15); elsif UPDATING and :NEW.STATE='APPROVED' THEN update TABLE2 set PAGE=16 AND STEP1='TEXT123' where TBLID1 =TABLE1.TBL1ID; end if; end; But I am not sure whether a trigger should be the solution here and how in general a trigger with an update statement can be created depending on column value of other table. Can you give me suggestions here?:)
Hello Sir! I would like to insert an entry into a column depending on the column of the same table. For that I thought of creating a trigger. This trigger checks which entry the certain column has in the table and then decides what to enter in another in the same table and in the same row. create or replace TRIGGER INSERT_COLUMN2_TABLE1 BEFORE INSERT OR UPDATE ON TABLE1 FOR EACH ROW begin -- IF :NEW.COLUMN ='REQUESTED' THEN :NEW.COLUMN2 := 'REQUEST_PENDING'; ELSEIF :NEW.COLUMN ='NOT_REQUESTED' THEN :NEW.COLUMN2:='TEXT123' END IF; END; but I get following errors when trying to execute the trigger: PLS-00103: Encountered the symbol "" when expecting one of the following: := . ( @ % ; PLS-00103: Encountered the symbol ";" when expecting one of the following: if as well I think this would be an after insert or update trigger? For the reason that it should be fired when an entry is already done in the column.. But how can I in general reference column in a trigger from the same table where I try to fire a trigger on? Maybe the better question is, how can one column value be populated based on another column in the same table. should I really use a trigger for that? --Used a virtual column for that ALTER table1 ADD ( column2 as ( case when column1 ...
Hello 🙂 I would like to create a history table in my application, which should indicate when which user has done what. For now, I have created an audit table and a trigger that enters the necessary data into this table after an UPDATE, DELETE or INSERT on the specific table. In the history table, however, I want to log every process step and fill it with the information WHO did WHAT WHEN and display it. This table should be updated in the course of the process, so that one can see which steps have already been taken and by whom. But now I am not sure how to create such a history table database-wise, because I want to log every process step executed by any user and display it in the history tab. Should I create a trigger for this as well and a table for each process step (although that would probably be a bad database design). I'm not asking for any code here, just a hint if it's possible and in best case some keywords so I can search for ...
Hello 🙂 I have created forms in which the user can enter data. With collections the information is saved and will be inserted in the corresponding tables after the forms are submitted. Now one column in the table has remained empty and I am not sure how to solve it in APEX. Namely, the table has a foreign key to another table. But the ID of this table is generated only after submitting the forms. Can I solve it, for example, with a trigger that then enters the foreign key into the table after the forms are submitted? Would it be an after insert trigger like this: CREATE OR REPLACE TRIGGER INSERT_FK AFTER INSERT ON TBL1 FOR EACH ROW begin INSERT INTO TBL2 VALUES (:NEW.ID); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line (TO_CHAR (SQLERRM (-20299))); WHEN OTHERS THEN DBMS_OUTPUT.put_line (TO_CHAR (SQLERRM (-20298))); end; or is there another better solution for this?
Hello:) I have a very general question. I am creating a process in which the user and other users have to go through several process steps. First step: User fills out and submits a form. By submitting a form, it should trigger an action which inserts data into a table. One Table is the described process step ("FORM_SUBMITTED", in here all relevant information will be stored, who submitted a form and when etc.) When the user logs in next time , he then sees that now another process step is initialized (for example another user must proceed an action). Question: For inserting data into the table, I thought of using a trigger, but I am not quite sure if this is the right way to go in APEX. When the user logs in next time, how can I display the current situation of a process to him (like in the picture, for example he sees that the second step in the process is initialized). I here thought of one solution: Creating a table which has following columns: the user-id and the different process steps. When a process step is done, the corresponding column in the table is set on true. When the user logs in next time, ...
Hi Sir, Good Morning,i have two tables table1 and table2 when field status of table1 is approved then it should insert into table2 i have used this trigger but not firing create or replace TRIGGER TRG_Times_INSERT AFTER INSERT ON Times FOR EACH ROW BEGIN IF :NEW.STATUS = 'APPROVED' then INSERT INTO APPROVE (PID,BILLING_STATUS,TASKS,MONDAY,LOGM,NOTEM,TUESDAY,LOGT,NOTET,WEDNESDAY,LOGW,NOTEW,THRUSDAY,LOGTH,NOTETH,FRIDAY,LOGF,NOTEF,TOTALHOURS,STATUS,COMMENTS) VALUES (:NEW.PID,:NEW.BILLING_STATUS,:NEW.TASKS,:NEW.MONDAY,:NEW.LOGM,:NEW.NOTEM,:NEW.TUESDAY,:NEW.LOGT,:NEW.NOTET,:NEW.WEDNESDAY,:NEW.LOGW,:NEW.NOTEW, :NEW.THRUSDAY,:NEW.LOGTH,:NEW.NOTETH,:NEW.FRIDAY,:NEW.LOGF,:NEW.NOTEF,:NEW.TOTALHOURS,:NEW.STATUS,:NEW.COMMENTS); END IF; END TRG_Times_INSERT;