Scooby-Doo Scooby-Doo - 1 year ago 93
SQL Question

PL/pgSQL Triggers - Insert\ Update only valid tuples

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';
end if;
return null;
$$language plpgsql;

create trigger T
before insert or update on event
for each row
execute procedure trigf();

The problem is that my function is able to detect an invalid date, but it doesn't insert\ update valid tuples.

What do I need to change in my trigger in order to achieve that?

Answer Source

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 return NULL:

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download