SAVEPOINTS
(1) By anonymous on 2021-02-10 11:57:08 [link]
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;
(2) By Kees Nuyt (knu) on 2021-02-10 12:45:58 in reply to 1 [link]
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.
(3) By Mark Lawrence (mark) on 2021-02-10 13:13:39 in reply to 1 [link]
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;
(4) By David Raymond (dvdraymond) on 2021-02-10 14:04:46 in reply to 1 [link]
Maybe because it's early in the morning, but when I read the `CASE 1 WHEN EMP_ID < 10 THEN ... ELSE ... ` 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 ... ELSE ... ` ? 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.
(5) By anonymous on 2021-02-10 14:05:34 in reply to 3
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;
(6) By Keith Medcalf (kmedcalf) on 2021-02-10 14:49:04 in reply to 1 [link]
Transaction Control statements are not allowed in a trigger body. The trigger body is already being executed within the context of a transaction.
(7) By anonymous on 2021-02-10 14:54:46 in reply to 6 [link]
Was under the impression that — since save points support nesting — that it would be possible. Maybe something to ponder about? Long-running triggers — a crazy complex update e.g. — could benefit from this…