I'm building this DB about the University for one of my course classes and I'm trying to create a trigger that doesn't allow for a professor to be under 21yo.

I have a Person class and then a Professor subclass.

What I want to happen is, you create a Person object, then a Professor object using that Person object's id, but, if the Person is under 21yo, delete this Professor object, then delete the Person object.

Everything works fine up until the "delete the Person object" part where this doesn't happen and I'm not sure why. Any help?

This is the sqlite code I have:

AFTER INSERT ON Professor
FOR EACH ROW
WHEN strftime('%J', 'now') - strftime('%J', (SELECT dateOfBirth from Person WHERE personId = NEW.personId)) < 7665 -- 21 years in days
BEGIN
    SELECT RAISE(ROLLBACK, 'Professor cant be under 21');
    DELETE FROM Person WHERE (personId= new.personId);

END;```

CodePudding user response：

One common issue is that there many not be a current transaction scope to rollback to, which would result in this error:

Error: cannot rollback - no transaction is active

If that occurs, then the trigger execution will be aborted and the delete never executed.

If ROLLBACK does succeed, then this creates a paradox, by rolling back to before the trigger was executed in a strictly ACID environment it would not be valid to continue executing the rest of this trigger, because the INSERT never actually occurred. To avoid this state of ambiguity, any call to RAISE() that is NOT IGNORE will abort the processing of the trigger.

CREATE TRIGGER - The RAISE()
When one of RAISE(ROLLBACK,...), RAISE(ABORT,...) or RAISE(FAIL,...) is called during trigger-program execution, the specified ON CONFLICT processing is performed and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the application, along with the specified error message.

NOTE: This behaviour is different to some other RDBMS, for instance see this explanation on MS SQL Server where execution will specifically continue in the trigger.

As OP does not provide calling code that demonstrates the scenario it is worth mentioning that in SQLite on RAISE(ROLLBACK,)

If no transaction is active (other than the implied transaction that is created on every command) then the ROLLBACK resolution algorithm works the same as the ABORT algorithm.

Generally, if you wanted to Create a Person and then a Professor as a single operation, you would Create a Stored Procedure that would validate the inputs first, preventing the original insert at the start.

To maintain referential integrity, even if an SP is used, you could still add a check constraint on the Professor record or raise an ABORT from a BEFORE trigger to prevent the INSERT from occurring in the first place:

BEFORE INSERT ON Professor
FOR EACH ROW
WHEN strftime('%J', 'now') - strftime('%J', (SELECT dateOfBirth from Person WHERE personId = NEW.personId)) < 7665 -- 21 years in days
BEGIN
    SELECT RAISE(ABORT, 'Professor can''t be under 21');
END

This way it is up to the calling process to manage how to handle the error. The ABORT can be caught in the calling logic and would effectively result in rolling back the outer transaction, but the point is that the calling logic should handle negative side effects. As a general rule triggers that cascade logic should only perform positive side effects, that is to say they should only affect data if the inserted row succeeds. In this case we are rolling back the insert, so it becomes hard to identify why the Person would be deleted.