Re: [sqlite] How to write the trigger?

[Tim Romano]

Michael,
In this particular instance my questions were posed rhetorically to the OP
after I had given his trigger a little more thought.  But I can be lazy, for
sure, so if you don't mind I'll keep your rebuke ready to hand for the next
time it's deserved, which should be soon as it's hot and humid here.

Regards
Tim Romano
Swarthmore PA


On Tue, Aug 3, 2010 at 9:10 AM, Black, Michael (IS)
wrote:

> You could've tested this in the time it took for you to get answer:
>
>
>
> From: sqlite-users-boun...@sqlite.org on behalf of Tim Romano
> Sent: Tue 8/3/2010 6:53 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] How to write the trigger?
>
>
>
> Is a to-be-inserted row supposed to be included among the counted rows in a
> BEFORE INSERT trigger? Could you end up with 5 rows?  What happens if you
> make this an AFTER trigger?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write the trigger?

[Black, Michael (IS)]

You could've tested this in the time it took for you to get answer:
 
File x.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (i integer);
INSERT INTO "t" VALUES(1);
CREATE TABLE c(i integer);
CREATE TRIGGER t1 before insert on t begin insert into c values((select 
count(*) from t));end;
CREATE TRIGGER t2 after insert on t begin insert into c values((select count(*) 
from t));end;
COMMIT;
 
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read x.sql
sqlite> insert into t values(2);
sqlite> select * from c;
1
2
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Tim Romano
Sent: Tue 8/3/2010 6:53 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] How to write the trigger?



Is a to-be-inserted row supposed to be included among the counted rows in a
BEFORE INSERT trigger? Could you end up with 5 rows?  What happens if you
make this an AFTER trigger?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write the trigger?

[Tim Romano]

Is a to-be-inserted row supposed to be included among the counted rows in a
BEFORE INSERT trigger? Could you end up with 5 rows?  What happens if you
make this an AFTER trigger?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write the trigger?

[Tim Romano]

Could your trigger be running into the syntax restriction described below?
I'm not sure if the restriction actually applies to your circumstance -- not
sure whether LIMIT and ORDER BY are invalid *anywhere* in a statement in a
trigger that deletes a row, i.e. invalid also in a subquery, as you have
done.

"The ORDER BY and LIMIT clauses on
UPDATE
 and DELETE  statements are not
supported. ORDER BY and LIMIT are not normally supported for
UPDATE
 or DELETE  in any context but can
be enabled for top-level statements using the
SQLITE_ENABLE_UPDATE_DELETE_LIMIT
compile-time
option. However, that compile-time option only applies to top-level
UPDATE
 andDELETE  statements, not
UPDATE
 and DELETE  statements within
triggers."

http://www.sqlite.org/lang_createtrigger.html


Regards
Tim Romano
Swarthmore PA



On Mon, Aug 2, 2010 at 10:45 PM, liubin liu <7101...@sina.com> wrote:

>
> I created a table like this：
> CREATE TABLE record_trip (trip_id CHAR(1), gp_no CHAR(1), rec_date INTEGER,
> trun CHAR(1), ctrl_id CHAR(1), moment_value INTEGER, minutes_value INTEGER,
> set_value INTEGER );
> CREATE UNIQUE INDEX i_record_trip ON record_trip (trip_id, gp_no,
> rec_date);
>
>
> And the trigger like：
> CREATE TRIGGER trig1 before insert on record_trip
> when (select count(*) from record_trip where trip_id=new.trip_id AND
> gp_no=new.gp_no)>4
> begin
> delete from record_trip where trip_id=new.trip_id AND gp_no=new.gp_no AND
> rec_date=(select rec_date from record_trip where trip_id=new.trip_id and
> gp_no = new.gp_no order by rec_date limit 1);
> end;
>
>
> I felt the when clause is wrong. It couldn't realize the intention of
> executing the trigger after the num of trip_id and gp_no is larger than 4.
> --
> View this message in context:
> http://old.nabble.com/How-to-write-the-trigger--tp29331491p29331491.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to write the trigger?

[liubin liu]

I created a table like this：
CREATE TABLE record_trip (trip_id CHAR(1), gp_no CHAR(1), rec_date INTEGER,
trun CHAR(1), ctrl_id CHAR(1), moment_value INTEGER, minutes_value INTEGER,
set_value INTEGER );
CREATE UNIQUE INDEX i_record_trip ON record_trip (trip_id, gp_no, rec_date);


And the trigger like：
CREATE TRIGGER trig1 before insert on record_trip 
when (select count(*) from record_trip where trip_id=new.trip_id AND
gp_no=new.gp_no)>4 
begin 
delete from record_trip where trip_id=new.trip_id AND gp_no=new.gp_no AND
rec_date=(select rec_date from record_trip where trip_id=new.trip_id and
gp_no = new.gp_no order by rec_date limit 1);
end;


I felt the when clause is wrong. It couldn't realize the intention of
executing the trigger after the num of trip_id and gp_no is larger than 4.
-- 
View this message in context: 
http://old.nabble.com/How-to-write-the-trigger--tp29331491p29331491.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users