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