Prevent insertion if condition is met

Question

I have a Content table like this:

 id | text | date | idUser → User | contentType 

And one more table Answer :

 idAnswer → Content | idQuestion → Content | isAccepted 

I want the Answer date to be longer than the Question date. Question: Content with contentType = 'QUESTION'.

I tried to solve this problem with the following trigger, but when I try to insert Answer , an error occurs:

 ERROR: record "new" has no field "idanswer" CONTEXT: SQL statement "SELECT (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idAnswer) < (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idQuestion)" PL/pgSQL function "check_valid_date_answer" line 2 at IF 

Trigger:

 CREATE TRIGGER check_valid_answer AFTER INSERT ON "Answer" FOR EACH ROW EXECUTE PROCEDURE check_valid_date_answer(); 

Trigger function:

 CREATE FUNCTION check_valid_date_answer() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idAnswer) < (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idQuestion) THEN RAISE NOTICE 'This Answer is an invalid date'; END IF; RETURN NEW; END;$$; 

So my question is: do I really need to create a trigger for this? I saw that I cannot use CHECK in Answer , because I need to compare with the attribute of another table. Is there any other (simpler / better) way to do this? If not, why is there a mistake and how can I solve it?

Answer 1

Your basic approach sounds. A trigger is a valid solution. It should work, except for 3 problems :

1) naming convention :

We will need to accurately determine the exact definition of the table, but there is evidence. the error message says: has no field "idanswer" is lowercase. Doesn't say "idanswer" - a case of CaMeL. If you create CaMeL case identifiers in Postgres, you must double them everywhere for the rest of your life.

2) Undo insert violation

• Raise EXCEPTION instead of friendly NOTICE to actually abort the whole transaction.

• Or RETURN NULL instead of RETURN NEW just disable the inserted row without raising an exception and without rolling back.

I would do the first. This will probably fix the error and work:

 CREATE FUNCTION trg_answer_insbef_check() RETURNS trigger AS $func$ BEGIN IF (SELECT c.date FROM "Content" c WHERE c.id = NEW. " idAnswer " ) < (SELECT c.date FROM "Content" c WHERE c.id = NEW. " idQuestion " ) THEN RAISE EXCEPTION 'This Answer is an invalid date'; END IF; RETURN NEW; END $func$ LANGUAGE plpgsql; 

the right decision is to use legal, lower names and completely eliminate such problems. This includes your failed table names, as well as the column name date , which is a reserved word in standard SQL and should not be used as an identifier - even if Postgres allows it.

3) There must be a BEFORE trigger

 CREATE TRIGGER insbef_check BEFORE INSERT ON "Answer" FOR EACH ROW EXECUTE PROCEDURE trg_answer_insbef_check(); 

You want to abort invalid inserts before doing anything else.

Of course, you will need to make sure that the Content timestamp table cannot be changed or you need more triggers to make sure your conditions are met.
The same goes for fk columns in Answer .

Answer 2

I would approach it differently.

Recommendation:

• use a trigger BEFORE INSERT if you want to change the data before inserting it
• use the AFTER INSERT trigger if you need to do extra work
• Use the CHECK clause if you have additional data consistency requirements.

So write a sql function that checks the condition that one date will be earlier than another, and add a check constraint. Yes, you can choose from other tables in your function.

I wrote something similar (a complex check) in response to this question on SO .