[SOLVED] SQLite3 Trigger Crashing
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2014-11-30 02:22

I'd rather slide down a pole of razor blades into a pool of alcohol
than have to ask for help...but I'm stuck.

I created this trigger:

Code: [Select]CREATE TRIGGER after_history_delete
   AFTER DELETE ON history
  BEGIN
    UPDATE ATTENDEE_OWES SET ATTENDEE_OWES_BALANCE = (SELECT
HISTORY_BALANCE FROM HISTORY ORDER BY HISTORY_ID DESC LIMIT 1) WHERE
OWES_ATTENDEE_ID = HIST_ATTENDEE_ID;
    DELETE FROM BALANCE;
    INSERT INTO BALANCE(BALANCE_AMT) SELECT
TOTAL(ATTENDEE_OWES_BALANCE) FROM ATTENDEE_OWES;
END
The trigger fails on the end of the UPDATE statement, telling me No
Such Column: HIST_ATTENDEE_ID.

Code: [Select]sqlite> .schema history
CREATE TABLE HISTORY(
                HISTORY_ID INTEGER PRIMARY KEY,
                LAST_PAYMENT_DATE VARCHAR NOT NULL,
                CURRENT_PAYMENT_DATE VARCHAR NOT NULL,
                SUBSIDY VARCHAR(1) NOT NULL,
                WEEKLY_PAYMENT REAL NOT NULL,
                AMOUNT_PAID REAL NOT NULL,
                HISTORY_BALANCE REAL NOT NULL,
                HIST_ATTENDEE_ID INTEGER,
                FOREIGN KEY(HIST_ATTENDEE_ID) REFERENCES
ATTENDEES(ATTENDEE_ID)
                ON DELETE CASCADE
                ON UPDATE CASCADE
);
I've tried HISTORY.HIST_ATTENDEE_ID & HISTORY(HIST_ATTENDEE_ID) and a
couple other things. Nothing works. Does anyone see anything I'm
missing here?

Thanks,
Landslyde

----------------------------------------------------------------------
goodname · 2014-11-30 02:51

MY guess is that HIST_ATTENDEE_ID does not exist in the ATTENDEE_OWES
table.

----------------------------------------------------------------------
guest48180 · 2014-11-30 03:23

Right you are, goodname. Here's the schema for attendee_owes:

Code: [Select]sqlite> .schema attendee_owes
CREATE TABLE ATTENDEE_OWES(
                OWES_ID INTEGER PRIMARY KEY,
                ATTENDEE_OWES_BALANCE REAL NOT NULL,
                OWES_ATTENDEE_ID INTEGER,
                FOREIGN KEY(OWES_ATTENDEE_ID) REFERENCES
ATTENDEES(ATTENDEE_ID)
                ON DELETE CASCADE
                ON UPDATE CASCADE
);
Both Foreign Keys in each of the tables, History and Attendee_Owes,
reference the Primary Key in the main table Attendees.

----------------------------------------------------------------------
guest48180 · 2014-11-30 22:56

Code: [Select]CREATE TRIGGER after_history_delete
   AFTER DELETE ON history
  BEGIN
    UPDATE ATTENDEE_OWES SET
    ATTENDEE_OWES_BALANCE = (SELECT HISTORY_BALANCE FROM HISTORY WHERE
OWES_ATTENDEE_ID = HIST_ATTENDEE_ID ORDER BY HISTORY_ID DESC LIMIT 1);
    DELETE FROM BALANCE;
    INSERT INTO BALANCE(BALANCE_AMT) SELECT
TOTAL(ATTENDEE_OWES_BALANCE) FROM ATTENDEE_OWES;
END
This works