On Saturday 27 March 2004 11:14 pm, Avner Levy wrote:
> I have a program (which I can't change) that every time a student fails
> in a course inserts a record like (34333, "math", 1).
...
B"H, I have a solution for you. Unfortunately, SQLite doesn't let you modify
the new.* or old.* inside a trigger, but you can accomplish what you want like
this:
-- File: test.sql
-- Description: Test file for Avner Levy
-- Author: Ron Aaron
-- Created: Sun 28 Mar 2004 11:21:21
-- Last updated: Mon 29 Mar 2004 08:46:02
BEGIN TRANSACTION;
CREATE TABLE grades ( id, course, failures );
CREATE TABLE helper (id, course, failures);
CREATE UNIQUE INDEX g_0 ON grades (id,course) ON CONFLICT REPLACE;
CREATE UNIQUE INDEX h_0 ON helper (id,course) ON CONFLICT REPLACE;
CREATE TRIGGER g_i BEFORE INSERT on grades
BEGIN
INSERT INTO helper
SELECT * from grades
WHERE id=new.id and course=new.course;
END;
CREATE TRIGGER g_i2 AFTER INSERT on grades
BEGIN
INSERT INTO grades
SELECT id,course,failures+1 from helper
WHERE id=new.id and course=new.course;
END;
COMMIT;
BEGIN TRANSACTION;
INSERT INTO grades VALUES (34333, "math", 1);
INSERT INTO grades VALUES (34333, "history", 1);
INSERT INTO grades VALUES (34333, "math", 1);
INSERT INTO grades VALUES (34333, "math", 1);
INSERT INTO grades VALUES (34333, "history", 1);
INSERT INTO grades VALUES (34333, "math", 1);
COMMIT;
SELECT "grades:", * FROM grades;
SELECT "helper:", * FROM helper;
Hope this helps,
Ron
--
My GPG public key is at http://ronware.org/
fingerprint: 4E91 06E9 2020 114C 8BCD 55B2 0816 60AF 2B3D 4C51
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
