SQLite Forum

SAVEPOINTS
Login

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

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;

(5) By anonymous on 2021-02-10 14:05:34 in reply to 3 [link]

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]

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.

(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…