Search code examples
postgresqlfunctiontriggershsqldb

Run triggered postgresql function on hsqldb


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


Solution

  • 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