Check value at insert

Question

Hello, i have this three tables.

Table: Item
Columns: ItemID, Title, Content, NoChange (Date)

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

In the Item Table is a Field with NoChange, if this field = true no Tag is allowed to insert a ItemTag value with this ItemID. How can i check this in the insert?

For Updates i have this Statement:

UPDATE ItemTag SET TagID = ? where ItemID = ? 
AND TagID = ? AND exists (
select ItemID from Item where ItemID = ? AND NoChange is null)");

Thank you.

Answer 1

The insert statement does not accept conditions:

INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ AS output_name ] [, ...] ]

What you could do is add a trigger to check if the conditions have been met and either rollback the transaction or skip the insert (the trigger should return null).

You could also write a relatively simple plpgsql procedure (which would be my choice if I was really bent on performing this check at the database level).

Answer 2

You can use the query-Syntax of the insert statement:

INSERT INTO ItemTag (TagID,ItemID) SELECT ?,? FROM item where itemid=? and nochange is null

where the first parameter is your tagid and the 2nd and 3rd your itemid.