I ran into a problem with savepoints. Are savepoints supported in a trigger? 33 SAVEPOINT X; 34 INSERT INTO AUDIT(EMP_ID, NOTICE) 35 VALUES ( 36 NEW.ID, 37 (SELECT CASE 1 38 WHEN EMP_ID < 10 THEN NEW.NAME || ' is too young to be an employee.' 39 ELSE 'OK.' 40 END) 41 ); 42 RELEASE SAVEPOINT X;
According to the syntax diagram for CREATE TRIGGER, SAVEPOINT is not a supported statement.
You can make a trigger execute conditionally with the WHEN expr syntax.
Also, RAISE() can influence the result of the statement that caused the trigger to fire.
By the way, your code example does not represent a trigger definition, and it is unclear what problem you experience.
Nothing to do with your SAVEPOINT query, but I just wanted to point out that you can INSERT ... SELECT directly without the extra VALUES and subquery:
INSERT INTO AUDIT(EMP_ID, NOTICE) SELECT NEW.ID, CASE 1 WHEN EMP_ID < 10 THEN NEW.NAME || ' is too young to be an employee.' ELSE 'OK.' END;
Wich is an excellent idea. So I refactored the complete trigger using your idea. CREATE TRIGGER C_CHECK_DATA BEFORE INSERT ON COMPANY BEGIN SELECT SF(0) WHERE NEW.AGE < 16; SELECT SF(1) WHERE LENGTH(NEW.ADDRESS) == 0; INSERT INTO AUDIT(EMP_ID, NOTICE) VALUES ( NEW.ID, (SELECT CASE 1 WHEN (FS(0) AND FS(1)) THEN NEW.NAME || 'is too young and has an invalid address FS(0)→ ' || FS(0) || ' FS(1)→ ' || FS(1) WHEN (FS(0) AND FC(1)) THEN NEW.NAME || ' is too young to be an employee. FS(0)→ ' || FS(0) || ' FS(1)→ ' || FS(1) WHEN (FC(0) AND FS(1)) THEN NEW.NAME || ' has an invalid address. FS(0)→ ' || FS(0) || ' FS(1)→ ' || FS(1) ELSE 'OK. FS(0)→ ' || FS(0) || ' FS(1)→ ' || FS(1) END) ); SELECT CF(0), CF(1); END;
(4) By David Raymond (dvdraymond) on 2021-02-10 14:04:46 in reply to 1 [link] [source]
Maybe because it's early in the morning, but when I read the
WHEN EMP_ID < 10 THEN ...
my brain kind of froze and I had to look up CASE again to get what you were doing. I'm curious as to why you would write it that way rather than
CASE WHEN EMP_ID < 10 THEN ...
It seems non-intuitive and works only in SQLite because of TRUE becoming 1, and wouldn't work in any DBMS which has a dedicated boolean type.
Transaction Control statements are not allowed in a trigger body. The trigger body is already being executed within the context of a transaction.