Reputation: 1621
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.
Upvotes: 0
Views: 4721
Reputation: 71
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();
Upvotes: 1
Reputation: 246238
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.
Upvotes: 1