In my project, I have two relations:
1. festival(title, start_date, end_date)
2. event(title, event_date)
I have to add a trigger that checks whenever a new tuple is being inserted\ updated to the event relation, if the event_date is set between the start_date and the end_date in the corresponding festival.
If the event row is invalid, then the trigger has to raise a notice, otherwise, the tuple has to be inserted\ updated regularly.
The trigger I made (in pl/pgSQL):
create function trigf() returns trigger as $$
if((NEW.event_date) < (select start_date from festival where festival.title = NEW.title) or
(NEW.event_date) > (select end_date from festival where festival.title = NEW.title)) then
raise notice 'The new event date is invalid';
create trigger T
before insert or update on event
for each row
execute procedure trigf();
You need to:
raise 'The new event date is invalid'instead of
raise notice 'The new event date is invalid'to force an error on invalid date instead of merely notice, which are only logged and then ignored.
return NEW, not
Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).