While playing around with the extension of the order_number question Dave posted
I got stuck at some point.
I have 3 triggers on a table for INSER/DELETE and UPDATE.
all 3 of them do further UPDATEs on the same table.
How can I make sure that these UPDATEs won't fire the UPDATE trigger again ?
Can I temporaily disable triggers or can I recognize when a they were
fired by statements
withing triggers ? Maybe I should use RAISE(IGNORE); but I don't know WHEN ?
Here is the full example:
CREATE TABLE players (
name VARCHAR(10),
ord INTEGER
);
CREATE TRIGGER keep_in_order_delete AFTER DELETE ON players
BEGIN
UPDATE players SET ord = ord-1 WHERE ord > old.ord;
END;
CREATE TRIGGER keep_in_order_insert BEFORE INSERT ON players
BEGIN
UPDATE players SET ord = ord+1 WHERE ord >= new.ord;
END;
CREATE TRIGGER keep_in_order_move BEFORE UPDATE OF ord ON players
BEGIN
UPDATE players SET ord = ord + 1 WHERE old.ord > new.ord AND ord <
old.ord AND ord >= new.ord;
UPDATE players SET ord = ord - 1 WHERE old.ord < new.ord AND ord >
old.ord AND ord <= new.ord;
END;
SELECT "Add players";
INSERT INTO players VALUES("a", 1);
INSERT INTO players VALUES("b", 2);
INSERT INTO players VALUES("c", 3);
INSERT INTO players VALUES("d", 4);
INSERT INTO players VALUES("e", 5);
SELECT * FROM players;
SELECT "Delete player in 2nd place:";
DELETE FROM players WHERE ord = 2;
SELECT * FROM players;
SELECT "Insert player in 2nd place:";
INSERT INTO players VALUES("x", 2);
SELECT * FROM players;
DROP TABLE players;
