On 11/02/2011, at 1:37 PM, Yuzem wrote:
> For example lets say I have the following data:
> 1|director|1
> 2|director|2
> 3|director|1
>
> In this example the total count for directors is 2, I have two distinct
> directors.
> In the table "Capacity Statistics" I will have:
> director|2
>
> The triggers you made add/subtract 1 from "Capacity Statistics" on
> insert/delete on "movies people"
> What happens if I add the following to "movies people"?
> 4|director|2
>
> The trigger should add 1 to "Capacity Statistics":
> director|3
>
> But there are still 2 directors:
> 1|director|1
> 2|director|2
> 3|director|1
> 4|director|2
Oh, I see. I was counting the total number of each unique capacity (including
director, writers), but you want the number of each unique (capacity, person).
No problem. We'll just add a People column to the statistics, and change the
uniqueness constraint.
Replace my earlier "Capacity Statistics" and triggers with this:
begin immediate
;
create table "Capacity People Statistics"
( ID integer primary key references "Capacity" (ID) on delete cascade
, People_ID integer not null references "People" (ID) on delete cascade
, Count integer not null
, unique (ID, People_ID)
)
;
insert into "Capacity People Statistics" (ID, People_ID, Count)
select Capacity_ID, People_ID, count(*) from "Movie People"
group by Capacity_ID, People_ID having Count > 0
;
create trigger "Movie People insert"
on "Movie People"
after insert
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
new.Capacity_ID
, new.People_ID
, ( select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where ID = new.Capacity_ID and People_ID = new.People_ID
)
;
end
;
create trigger "Movie People delete"
on "Movie People"
after delete
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
old.Capacity_ID
, old.People_ID
, ( select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where ID = old.Capacity_ID and People_ID = old.People_ID
)
;
end
;
create trigger "Movie People update"
on "Movie People"
after update of Capacity_ID, People_ID
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
old.Capacity_ID
, old.People_ID
, ( select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where ID = old.Capacity_ID and People_ID = old.People_ID
)
;
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
new.Capacity_ID
, new.People_ID
, ( select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where ID = new.Capacity_ID and People_ID = new.People_ID
)
;
end
;
commit
;
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
