Are database triggers safe for cross table integri

Question

I suggested using a trigger to check a cross table integrity constraint answering this question. It has been suggested in the comments that it may cause problems:

Triggers to do cross row checks rarely work on most databases ... because they cannot read uncommitted rows from other transactions

I haven't found any source supporting the claim, though. Official documentation doesn't mention anything. Others issues I found are covered here on SO - it mostly criticizes potential hidden complexity as triggers are not visible on first sight. Bet even the highest rated answer admits their usage for integrity issues.

So my question is: Are database triggers safe for cross table integrity constraints? Specially, would the solution below work?

---

To summarize the original question. We have tables

• Player - PlayerID, PlayerName
• Bet - BetID, BetName
• plays_in - BetID, PlayerID

The constraint it that the combination of BetName and PlayerID sholud be unique. Definition of the suggested trigger:

CREATE TRIGGER check_bet_name BEFORE INSERT ON plays_in 
  FOR EACH ROW BEGIN
      DECLARE bet_exists INT DEFAULT 0;
      DECLARE msg VARCHAR(255);

      SELECT 1 INTO bet_exists 
        FROM Bet AS b1
        WHERE b1.BetID = NEW.BetID
          AND EXISTS (SELECT * 
            FROM plays_in AS p JOIN Bet AS b2 USING (BetID)
            WHERE p.PlayerID = NEW.PlayerID AND b2.BetName = b1.BetName
          )
        LIMIT 1;

      IF bet_exists THEN
        SET msg = "Bet name already exists...";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
      END IF;
  END//

Answer 1

It probably depends on which database and how well you write the logic.

Early versions of infomodeler/visiomodeler supported some pretty arcane and complex forms of referential integrity, and provided the code to implement them on several databases. Early versions of Sybase / sql server didn't support declarative referential integrity so all the logic was implemented in triggers - successfully.

I wouldn't take the failure of one implementation of a counter-example as authoritative.

Answer 2

The answer is triggers are not safe.

It turns out that the trigger really doesn't see uncommited changes done in other transactions and passes without an error. It can be demonstrated like this

Transaction 1:

START TRANSACTION;
INSERT INTO plays_in (BetID, PlayerID) VALUES (1,1); -- query A

Transaction 2:

START TRANSACTION;
INSERT INTO plays_in (BetID, PlayerID) VALUES (1,2); -- query B; in conflict with A, but passses

Both transactions:

COMMIT;

Now plays_in will contains both inserted records even though if A and B were executed in a single transaction, the trigger would throw an error.

The whole example sources can be obained here