2009/5/9 Sam Carleton <scarle...@miltonstreet.com>:
> This is my first BEFORE INSERT trigger in SQLite and I am getting an error:
>
> SQL error: near "new": syntax error
>
> My goal is that on an insert only the insertedby value is provide.
> The trigger will set that to the updatedby, insertedon and updatedon
> fields. I searched the web and the only examples I could find was of
> an AFTER INSERT, am I better off with that approach? I would think
> not.
An UPDATE on a record can only work AFTER it has been inserted.
>
> Here is SQL for the table and trigger:
> ---------------------------------------------------------------
> CREATE TABLE Customer (
> CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
> IsInSlideShow INTEGER NOT NULL,
> Username CHAR(50) NOT NULL,
> Password CHAR(50),
> insertedby CHAR(50) NOT NULL,
> instertedon DATE NOT NULL,
> updatedby CHAR(50) NOT NULL,
> updatedon DATE NOT NULL,
> UNIQUE (username));
>
> CREATE TRIGGER ti_Customer BEFORE INSERT ON Customer
> BEGIN
> new.instertedon = DATETIME('NOW');
> new.updatedon = new.instertedon;
> new.updatedby = new.insertedby;
> END;
The trigger must be a valid sql statement.
As in:
sqlite> create table tst( id integer primary key, data real,
insertDate real, updateDate real );
sqlite> create trigger tstTrig after insert on tst begin update tst
set updateDate=insertDate where id=new.id; end;
sqlite>
sqlite> insert into tst( data, insertDate ) values( 1, julianday( '2009-05-09 12
:00:00' ) );
sqlite> insert into tst( data, insertDate ) values( 2, julianday( '2009-05-09 13
:00:00' ) );
sqlite>
sqlite> select * from tst;
1|1.0|2454961.0|2454961.0
2|2.0|2454961.04166667|2454961.04166667
sqlite>
Rgds,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
