I can't find solution about transfering my function.
Lets manage working function and trigger on postgresql as below:
CREATE FUNCTION func_check_minutes() RETURNS trigger AS
$$
BEGIN
IF (SELECT minutes + NEW.minutes FROM employees WHERE date = NEW.date) > 50
THEN RETURN NULL;
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
CREATE TRIGGER tr_check_minutes
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE PROCEDURE func_check_minutes();
Is it even possible to run this function on hslqdb?
Because when I try to run it (obviously without language command) there is an error:
DatabaseException: unexpected token: TRIGGER
I have syntax error, so I dont know if it's even possible. I was reading about functions and triggers in hsqldb from documentation, but did'nt notice any example about triggered functions in hsqldb.
With help from @fredt I created query:
<sql dbms="hsqldb">
DROP TRIGGER IF EXISTS tr_check_minutes
CREATE TRIGGER tr_check_minutes
BEFORE INSERT ON hours_worked
FOR EACH ROW
BEGIN ATOMIC
IF (SELECT sum(minutes) + NEW.minutes FROM hours_worked WHERE date = NEW.date) > 1440
THEN RETURN NULL;
END IF;
RETURN NEW;
END
</sql>
But it prints an error: user lacks privilege or object not found: NEW.DATE
If you want the INSERT to fail when too many hours are worked, you can throw an exception:
CREATE TRIGGER tr_check_minutes
BEFORE INSERT ON hours_worked
REFERENCING NEW ROW AS NEW
FOR EACH ROW
BEGIN ATOMIC
IF (SELECT sum(minutes) + NEW.minutes FROM hours_worked WHERE date = NEW.date) > 1440
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'too many hours';
END IF;
END