Trigger on date in postgresql

Question

I'm reading a lot of articles how to create triggers, but I really have no clue how to create trigger depending on date.

I want to add a trigger that: 1. check before add or update if the sum of minutes is higher than 50 on a specific day.

So in example entity:

CREATE TABLE employees(
   id int serial primary key,
   minutes int NOT NULL,
   date date NOT NULL
);

this is a typical data in db:

(1, 40, '2018-01-1')
(2, 30, '2018-01-2')
(3, 20, '2018-01-3')
(4, 10, '2018-01-4')

now if I add:

(6, 20, '2018-01-1')
(7, 40, '2018-01-2')
(8, 20, '2018-01-3')
(9, 20, '2018-01-4')

the final result would've be:

(1, 40, '2018-01-1')
(2, 30, '2018-01-2')
(3, 20, '2018-01-3')
(4, 10, '2018-01-4')
(8, 20, '2018-01-3')
(9, 20, '2018-01-4')

id with 6 and 7 are omitted (not added, because they are higher than 50)

I really appreciate a help with it :)

update: with help from @Laurenz Albe I created function and trigger as below:

        CREATE FUNCTION func_check_minutes() RETURNS trigger AS
        $$
        BEGIN
        IF (SELECT sum(minutes) + NEW.minutes FROM employees WHERE date = NEW.date) > 50
        THEN RETURN NULL;
        END IF;
        END;
        $$
        LANGUAGE 'plpgsql';

        CREATE TRIGGER tr_check_minutes
        BEFORE INSERT ON employees
        FOR EACH ROW
        EXECUTE PROCEDURE func_check_minutes();

But this solution fails without a clear reason.

That error has nothing to do with the trigger, it is from your Java code. Try directly on the database with psql. — Laurenz Albe
– Laurenz Albe, Commented Jan 31, 2018 at 10:44

Anand A · Accepted Answer · 2018-01-31 18:22:48Z

1

As you have not mentioned what fails, i am assuming that nothing is being written to the table. You will have to add RETURN NEW after the END IF

I have updated the trigger

CREATE FUNCTION func_check_minutes() RETURNS trigger AS
        $$
        BEGIN
        IF (SELECT sum(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();

answered Jan 31, 2018 at 18:22

Anand A

713 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

degath Over a year ago

and truelly this can be solution (let me check it and I'll write progress soon)

Laurenz Albe · Accepted Answer · 2018-01-31 07:57:00Z

1

The central piece of such a trigger would be:

IF (SELECT sum(minutes) + NEW.minutes
    FROM employees
    WHERE date = NEW.date) > 50
THEN
   <throw an error or return NULL>
END IF;

But you should be aware that there is a race condition that could cause the condition to be violated:

If the triggers from two inserts run concurrently, each of them won't see the effects of the other transaction, and both could report success while the final result after both transactions are committed could exceed the limit.

The only way to get around that would be using SERIALIZABLE transactions.

answered Jan 31, 2018 at 7:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Trigger on date in postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related