HSQL trigger with multiple when clause

Question

Can we create Trigger in HSQL DB, with multiple WHEN clause. Something like this :-

CREATE TRIGGER  perosn_trig AFTER UPDATE ON person
REFERENCING  NEW AS nwrow  OLD as oldrow
FOR EACH ROW 
   when ( nwrow.person_id>100 )  
   ( insert into TRIGLOG values ('PERSON_more_than_100',nwrow.person_id,SYSDATE)  ),
   When (nwrow.person_id<=100) 
   ( insert into TRIGLOG values ('PERSON_less_than_100',nwrow.person_id,SYSDATE) )
;

This query gives syntax errors. What will be correct syntax ?

Answer 1

Currently there is no support for multiple WHEN clauses in a trigger. The WHEN clause is generally used with a simple condition to call the trigger only when necessary.

For more complex conditions use a CASE or IF condition:

CREATE TRIGGER  perosn_trig AFTER UPDATE ON person
REFERENCING  NEW AS nwrow  OLD as oldrow
FOR EACH ROW 
BEGIN ATOMIC
  IF ( nwrow.person_id>100 ) THEN 
    insert into TRIGLOG values ('PERSON_more_than_100',nwrow.person_id,SYSDATE);
  ELSE
    insert into TRIGLOG values ('PERSON_less_than_100',nwrow.person_id,SYSDATE);
  END IF;
END

http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_conditional