SQLite Trigger Problem

Databases
1

I have an SQLite database. I need to create a historical line in a table following the update of another table.

Primary table:

CREATE TABLE Budgets( mKey INTEGER NOT NULL PRIMARY KEY, BudName TEXT, StartDate DateTime, BudAmount Double )
Historical table:

CREATE TABLE BudHist( mKey INTEGER NOT NULL PRIMARY KEY, BudKey INTEGER, StartDate DateTime, BudAmount Double )
After an update of the Budgets table (BudAmount) I need the old data to be placed in the BudHist table. I am attempting to set a trigger with this code:

Create Trigger UpdateHistory after update of BudAmount on Budgets begin insert into BudHist (BudKey, StartDate, BudAmount) values (old.mKey, old.startdate, old.budamount); end
which, according to the SQLite help should work.

However, when I execute that command I get

error near ")": syntax error

Can anybody give me some hint as to what I am doing wrong?

Thanks all.

Simon.

2

Are you sure that is the exact code you are executing?

CREATE TABLE Budgets(
 mKey INTEGER NOT NULL PRIMARY KEY,
 BudName TEXT,
 StartDate DateTime,
 BudAmount Double
);

CREATE TABLE BudHist(
 mKey INTEGER NOT NULL PRIMARY KEY,
 BudKey INTEGER,
 StartDate DateTime,
 BudAmount Double
);

Create Trigger UpdateHistory after update of BudAmount on Budgets
begin
 insert into BudHist (BudKey, StartDate, BudAmount) values (old.mKey, old.startdate, old.budamount);
end;

INSERT INTO budgets VALUES (1, 'Hi', '2015-01-02', 100.0);
UPDATE budgets SET BudAmount = 200.0 WHERE mKey = 1;

SELECT * FROM budgets;
SELECT * FROM budhist;

when run from the console works as expected:

$ rm hi.db ; sqlite3 hi.db < hi.sql 
1|Hi|2015-01-02|200.0
1|1|2015-01-02|100.0

Also, on a side note, you may want to add a timestamp to the BudHist table so you can know when those amounts were changed.

3

Yes it is.

But -

I have found that if I run the command directly against the database it works. It has to be something with the SQL db editor I am using as the trigger is there now.

Thank you, problem solved.

Simon.