hello list,
my problem is rather related to sql in general than to sqlite but as I'm
using sqlite as my db and there are so many authorities of sql in this
list I thought of asking my question here. I have the following schema:
CREATE TABLE T_Linien(
handle TEXT
, von TEXT
, nach TEXT
, objektartengruppe TEXT
, UNIQUE (von, nach, objektartengruppe));
with two triggers:
CREATE TRIGGER tr_sort_linien_insert BEFORE INSERT ON T_Linien
WHEN NEW.von > NEW.nach
BEGIN
INSERT INTO T_Linien (von, nach, objektartengruppe) VALUES
(NEW.nach, NEW.von, NEW.objektartengruppe);
SELECT RAISE(IGNORE);
END;
CREATE TRIGGER tr_sort_linien_update BEFORE UPDATE ON T_Linien
WHEN NEW.von > NEW.nach
BEGIN
UPDATE T_Linien
SET handle = NEW.handle
WHERE
(
von = NEW.nach
AND nach = NEW.von
AND objektartengruppe = NEW.objektartengruppe
);
SELECT RAISE(IGNORE);
END;
My goal is that 'von' always contains values that are smaller than
'nach'. The following sql inserts the values correctly in that it
exchanges 'von' and 'nach'.
INSERT INTO T_Linien (von, nach, objektartengruppe) VALUES (
'c'
, 'b'
, 'xx');
SELECT * FROM T_Linien;
handle von nach objektartengruppe
b c xx
Unfortunately the update doesn't quite work as I expected it to do:
UPDATE T_Linien
SET handle = '0815'
WHERE von = 'c'
AND nach = 'b'
AND objektartengruppe = 'xx';
SELECT * FROM T_Linien;
handle von nach objektartengruppe
b c xx
Isn't NEW.von 'c' and NEW.nach 'b' and shouldn't in that case exchange
the update trigger the two values?
Any help is really appreciated, thanks in advance, Bernd
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
