How to write Trigger in HSQLDB to change 2 field (columns) values in a table of 6 fields (columns) before insert of any new record in that table?

Question

I have a table ABC with 6 fields (columns as a1,a2,a3,a4,a5) and Log_ID as primary key (6th column). I have another table XYZ with Log_ID as foreign key in it. I want a trigger at BEFORE INSERT (because a thread is monitoring insertions) to ensure that before insertion of new record in ABC and XYZ, the values in a3 and a4 of ABC should be overwritten BASED UPON the value of a2 , to what I specify in trigger on runtime. For Instance I want that every time a document coming as a2='incoming' with a3='root' and a4='123456' should be overwritten with a3='user1' and a4='abcd@xyz' ...and like wise a document coming as a2='outgoing' with a3='user' and a4='6789' should be overwritten with a3='root' and a4='1234@3456' Will I write separate triggers for both cases or both cases can be accommodated in 1 trigger?? and How will i write these trigger(s) BEFORE INSERT???

Answer 1

You need only one trigger on each table. The trigger will look like the example below:

CREATE TRIGGER trigone BEFORE INSERT ON abc 
  REFERENCING NEW ROW AS newrow FOR EACH ROW
  BEGIN ATOMIC
    IF newrow.a2 = 'incoming' AND newrow.a3 = 'root' THEN   
      SET newrow.a3 = 'user1';
    END IF;

    IF newrow.a2 = 'outgoing' THEN   
      SET newrow.a3 = 'root';
      SET newrow.a4 = '123@234';
    END IF;
  END