SQLite Forum

Sqlite3 Trigger
Login

Sqlite3 Trigger

(1) By anonymous on 2020-12-09 17:46:30 [source]

CREATE TRIGGER InsertfehlerPruefung BEFORE INSERT ON Pruefung BEGIN SELECT CASE WHEN (NEW.theorie = 0 AND NOT (SELECT SUM(dauer) FROM fahrstunde WHERE fahrstunde.schueleremail = NEW.email ) >= 180) THEN RAISE(ABORT, 'not enough Fahrstundenminuten') END; END;

I have this before insert on trigger here and it just doesnt wanna work. I try to insert a "schueleremail" who has less than 180 "fahrstundenminuten" and the trigger wont work and the insert is still successful.

can please someone help?

(2) By David Raymond (dvdraymond) on 2020-12-09 17:59:01 in reply to 1 [link] [source]

Is it an order of operations thing where it's parsing it as...

NEW.theorie AND (NOT (...)) >= 180

...?

Try making it < 180, or adding some parenthesis.

The unary prefix operators are on their own line in Operators, so I'm not really sure where they stand in the list.

(3) By anonymous on 2020-12-09 18:12:58 in reply to 2 [link] [source]

The context is like this:

I have a table named "Pruefung" where the Attribute "Theorie" is a boolean(this tells you, if a drivers license test is theory (when 1) oder practical(when 0)).

Then there is a table called "Schueler" with an Attribute called "Email" Then a Table named "Fahrstunde" with an attribute called "dauer" which tells you, how long a practical lesson took (in minutes)

Now before an insert on Pruefung can happen, i have to make sure that the "schueler"(which is german for student) has at least 180 minutes of pratical driving lessons.

Here are the tables: CREATE TABLE IF NOT EXISTS Pruefung ( PruefungsID int not null primary key check(PruefungsID > 0) , Theorie boolean not null, Gebuehr float not null check(Gebuehr > 0 and round(Gebuehr, 2) = Gebuehr), Bestanden boolean not null, Email varchar[50] not null collate nocase ,

foreign key(Email) references Schueler(Email) on update cascade on delete cascade

);

CREATE TABLE IF NOT EXISTS Schueler ( Email varchar[50] primary key not null collate nocase Geschlecht varchar[50] not null collate nocase check(length(Geschlecht)>0 and Geschlecht in('m', 'w', 'd')), AdressID int not null check(AdressID > 0),

foreign key(Email) references Nutzer(Email) on update cascade on delete cascade,
foreign key(AdressID) references Adresse(AdressID) on update cascade on delete cascade

);

CREATE TABLE IF NOT EXISTS Fahrstunde ( FahrstundeID int primary key not null check(FahrstundeID > 0), Typ varchar[50] not null collate nocase check(length(Typ)>0 and Typ not glob 'misref'), Dauer int not null check(Dauer > 0 and Dauer % 45 = 0), Preis float not null check(Preis > 0 and round(Preis, 2) = Preis), Schueleremail varchar[50] not null collate nocase , Fahrlehreremail varchar[50] not null collate nocase , Fahrschulemail varchar[50] not null collate nocase ,

foreign key(Schueleremail) references Schueler(Email) on update cascade on delete cascade,
foreign key(Fahrlehreremail) references Fahrlehrer(Email) on update cascade on delete cascade,
foreign key(Fahrschulemail) references Fahrschule(Email) on update cascade on delete cascade

);

when i try to insert in Pruefung a student with the emailadress " schueler6@web.de", it should not work because he only has 90 minutes. but it does get inserted...even though i wrote this trigger. and i just can find the error and hope for help :D


  1. ^ Misreference

(4) By anonymous on 2020-12-09 18:16:20 in reply to 2 [link] [source]

oh btw i forgot to mention (or rather i have a copy paste error in the trigger), i did make it to < 180. its because this doesnt work..i thought i try >= 180 :D

(5) By Keith Medcalf (kmedcalf) on 2020-12-09 19:14:03 in reply to 1 [link] [source]

The code fragment "NOT (SELECT SUM(dauer) FROM fahrstunde WHERE fahrstunde.schueleremail = NEW.email ) >= 180"

will always evaluate to FALSE, so the result of ANDing that with anything will also be FALSE. This means that the entire condition will always be FALASE, the WHEN clause will always fail (be FALSE) and the RAISE will never be executed.

NOT (something)

will ALWAYS be either 1 (true) or 0 (false) and 0/1 is NEVER > than 1.

(6) By Keith Medcalf (kmedcalf) on 2020-12-09 19:26:59 in reply to 1 [link] [source]

If you write the trigger without the unnecessary convoluted crappola, what happens?

CREATE TRIGGER InsertfehlerPruefung BEFORE INSERT ON Pruefung 
BEGIN 
   SELECT RAISE(ABORT, 'not enough Fahrstundenminuten')
    WHERE NEW.theorie == 0 
      AND (
           SELECT SUM(dauer) 
             FROM fahrstunde 
            WHERE fahrstunde.schueleremail == NEW.email
          ) >= 180;
END;

I don't speak whatever language those variables are in but I suspect that your >= comparison is wrong-about.

I therefore suspect that your whole logic is broken and I would only comment that convoluted thinking and complication for complication sake does not usually result in what was intended. In other words, embrace the KISS principle!

(7) By anonymous on 2020-12-09 19:58:51 in reply to 6 [link] [source]

Hi, first of all, thank you for your help. Much appreciated.

so... i think i forgot to mention: the first versiion of my trigger was this: CREATE TRIGGER 'InsertfehlerPruefung' BEFORE INSERT ON Pruefung BEGIN SELECT CASE WHEN (NEW.theorie = 0 AND (SELECT SUM(dauer) FROM fahrstunde WHERE fahrstunde.schueleremail = NEW.email ) < 180) THEN RAISE(ABORT, 'Nicht genug Fahrstundenminuten') END; END;

it was only when it didnt work that i changed it to "not >= 180".

Now.. i tried out your version with an emailadress from a student who only has 90minutes and it still gets inserted.

(8) By Keith Medcalf (kmedcalf) on 2020-12-09 20:50:55 in reply to 7 [link] [source]

How do you know that you are inserting a value of 0 into Pruefung.theorie? If the value is not 0 then the trigger will never execute the RAISE(ABORT ...).

There is no such type as "boolean" and you have no constraints on this field so your could insert whatever you want in it without raising an error, as long as the value inserted is NOT NULL, as that is the ONLY constraint on its value.

In other words there is nothing to ensure that you are not trying to insert the text string 'bloody well not!', which would NEVER equal 0 and the trigger would NEVER actuate (this is true of all column affinities so if you REQUIRE a specific value(s) you should make sure that the value meets your requirement).

You also do not mention which version of SQLite3 you are using as there have been changes in the past several years with regard to when column affinities are applied and checked.

(11) By anonymous on 2020-12-09 21:55:32 in reply to 8 [link] [source]

oh right..my sqlite3 version is this: SQLite version 3.32.2

and as far as I know, sqlite does know boolean as datatype so i used boolean when i created the table.

CREATE TABLE IF NOT EXISTS Pruefung ( PruefungsID int not null primary key check(PruefungsID > 0) , Theorie boolean not null, Gebuehr float not null check(Gebuehr > 0 and round(Gebuehr, 2) = Gebuehr), Bestanden boolean not null, Email varchar[50] not null collate nocase,

foreign key(Email) references Schueler(Email) on update cascade on delete cascade

);

(13) By Gunter Hick (gunter_hick) on 2020-12-10 09:51:22 in reply to 11 [link] [source]

from https://sqlite.org/datatype3.html

"2.1. Boolean Datatype SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true)."

Your type name BOOLEAN translates to NUMERIC affinity, i.e. SQLite will store "strings that look like numbers" as INTEGER or REAL if possible. You could literally store 'any shit' in Theorie and it would come out NOT NULL.

BTW:

"PruefungsID int not null primary key check(...)" is better formulated as "PruefungsID integer primary key" which makes it an alias for the rowid, which is guaranteed to be NOT NULL and greater than zero. It also allows faster lookups.

"Gebuehr float not null check(...)" is not a good way to store "money". Not all decimal fractions have an exact representation in binary floating point, and so totalling up all Gebuehr values may exhibit rounding errors. Money is usually stored in the lesser unit (Cents instead of Euro), leaving the formatting up to the presentation layer, where it belongs.

"Email varchar[50]" is better written as text, because SQLite stores strings of arbitrary length (up to the supported maximum) and does not enforce the declared length. If your application only supports 50 character strings, then you need to add "check(length(Email) < 50)"

(15) By Ryan Smith (cuz) on 2020-12-10 10:05:21 in reply to 13 [link] [source]

is better formulated as "PruefungsID integer primary key" which makes it an alias for the rowid, which is guaranteed to be NOT NULL and greater than zero.

Not fully true with respect to "... greater than zero":

CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT);

INSERT INTO t(a,b) VALUES (-1,'Minus One'),(0,'Zero'), (1,'One'),(2,'Two');

SELECT * FROM t;


  --   a |b          
  -- ----|-----------
  --  -1 |Minus One  
  --   0 |Zero       
  --   1 |One        
  --   2 |Two        

DROP TABLE t;

I think the only restriction is that it HAS to be an Integer that fits inside a 64-bit signed value range/space.

(17) By Gunter Hick (gunter_hick) on 2020-12-10 10:22:26 in reply to 15 [link] [source]

Ok I'll add "if you leave it up to SQLite to choose the values"

(18) By anonymous on 2020-12-10 10:42:11 in reply to 13 [link] [source]

for PruefungsID: i added not null because my uni-tutor said so :D i manually insert a 0 into theorie when inserting values for the table.

what is the point of varchar when it doesnt enforce the specified length without the check?

(20) By Stephan Beal (stephan) on 2020-12-10 10:50:03 in reply to 18 [link] [source]

what is the point of varchar when it doesnt enforce the specified length without the check?

sqlite (note the LITE part) supports various standardized data type names for syntactic compatibility with non-lite SQL systems, but it's up to the application to enforce any such requirements.

(25) By Tim Streater (Clothears) on 2020-12-10 16:31:24 in reply to 18 [link] [source]

A better question is: "What is the point of varchar?"

(26.1) By Keith Medcalf (kmedcalf) on 2020-12-10 16:43:15 edited from 26.0 in reply to 25 [link] [source]

"varchar" is the same as the type "Humpback Character Giraffes" in that it contains the string "char" that indicates the affinity is text. Otherwise it is just an arbitrary string of characters.

https://sqlite.org/datatype3.html

varchar(n) may have other implementation specific meanings in other database management systems.

** Edited to fix a typo of the " mark

(9) By Keith Medcalf (kmedcalf) on 2020-12-09 21:07:26 in reply to 7 [link] [source]

Try this one:

CREATE TRIGGER InsertfehlerPruefung BEFORE INSERT ON Pruefung 
BEGIN 
   SELECT RAISE(ABORT, 'not enough Fahrstundenminuten')
    WHERE NEW.theorie == 0 
      AND (
           SELECT SUM(dauer) 
             FROM fahrstunde 
            WHERE fahrstunde.schueleremail == NEW.email
          ) < 180;
   SELECT RAISE(ABORT, 'theorie is not 0')
    WHERE NEW.theorie IS NOT 0;
END;

and see what happens.

(10) By anonymous on 2020-12-09 21:52:34 in reply to 9 [link] [source]

as to how i know theorie is 0: I manually insert a 0 like this: INSERT INTO Pruefung(PruefungsID, Theorie, Gebuehr, Bestanden, Email) VALUES(4, 0, 15.5, 1, 'schueler6@web.de'); and this should not be inserted if the trigger works right because schueler6@web.de has only 90 minutes.

i tried with your second version now. It still inserts the schueler6@web.de where theorie is 0.

But it does not insert this: INSERT INTO Pruefung(PruefungsID, Theorie, Gebuehr, Bestanden, Email) VALUES(5, 1, 10, 1, 'schueler6@web.de'); which is what your second version of the trigger wants in the last part.

but it actually is all about when Theorie is 0.

what happens when theorie is 1 is another trigger that is only supposed to work, when the student hast at least done 3 distinct theorylessons..but well..thats another trigger that i didnt write yet.

(12) By Keith Medcalf (kmedcalf) on 2020-12-09 23:13:36 in reply to 10 [link] [source]

What is the result of:

SELECT SUM(dauer) 
  FROM fahrstunde 
 WHERE fahrstunde.schueleremail == 'schueler6@web.de';

(14) By anonymous on 2020-12-10 10:02:25 in reply to 12 [link] [source]

it gives me the total amount of "fahrstundenminuten" and the result is 90, which is clearly less than 180 =)

(16) By Gunter Hick (gunter_hick) on 2020-12-10 10:19:35 in reply to 12 [link] [source]

I'm going to guess NULL. Should have used total(dauer).

(19) By anonymous on 2020-12-10 10:48:39 in reply to 16 [link] [source]

I do get 90 for the query. sum works fine. I tried the Trigger with "TOTAL(dauer)"

Now it wont insert anything. not even those students, that have more than 180. i just cant understand what the problem is :(

(21.1) By Gunter Hick (gunter_hick) on 2020-12-10 14:09:56 edited from 21.0 in reply to 19 [link] [source]

Did you check what the select total(dauer)... returns before stuffing it into the trigger program? Did you check what select *... returns to see the records that match the criteria? You could also try dropping the trigger, doing the insert, and checking that the SELECT from the trigger produces a result.

(23) By anonymous on 2020-12-10 14:55:03 in reply to 21.1 [link] [source]

yes i tried thie trigger query as a normal query in sqlite. it did return 90 for schueler6 to me. then i used it in the trigger, but it still wont activate on schueler6(whose value is only 90)

(22) By Keith Medcalf (kmedcalf) on 2020-12-10 14:22:51 in reply to 19 [link] [source]

You talk about inserting and not inserting things, when what should actually be happening is that "I get an error which says ..." or "I do not get an error condition and the operation succeeds".

The fact that you think a record was inserted is irrelevant.

How about you try this trigger and tell what the error message is:

CREATE TRIGGER InsertfehlerPruefung BEFORE INSERT ON Pruefung 
BEGIN 
   SELECT RAISE(ABORT, 'not enough Fahrstundenminuten')
    WHERE NEW.theorie == 0 
      AND (
           SELECT SUM(dauer) 
             FROM fahrstunde 
            WHERE fahrstunde.schueleremail == NEW.email
          ) < 180;
   SELECT RAISE(ABORT, 'theorie is not 0')
    WHERE NEW.theorie IS NOT 0;
   SELECT RAISE(ABORT, 'sum(dauer) ge 180')
     WHEN (
      AND (
           SELECT SUM(dauer) 
             FROM fahrstunde 
            WHERE fahrstunde.schueleremail == NEW.email
          ) >= 180;
   SELECT RAISE(ABORT, 'sum(dauer) lt 180')
     WHEN (
           SELECT SUM(dauer) 
             FROM fahrstunde 
            WHERE fahrstunde.schueleremail == NEW.email
          ) < 180;
   SELECT RAISE(ABORT, 'sum(dauer) IS NULL')
     WHEN (
           SELECT SUM(dauer) 
             FROM fahrstunde 
            WHERE fahrstunde.schueleremail == NEW.email
          ) IS NULL;
END;

Because for that you MUST get an error. I suspect that you are mistaken and making assumptions from unverified facts not in evidence.

(24) By anonymous on 2020-12-10 15:36:30 in reply to 22 [link] [source]

Ok Update:

I managed to get it to work. it works perfectly now.

The problem was that in my data.sql file where my inserts are, the order of insertions were wrong! Thus the trigger didnt activate. now that i corrected it, both triggers work!

Thank you all for your time and help! much appreciated.