Hi All,
I'm using triggers to make my views updatable (ie the user can edit
the values view because SQLite will instead change the related source
table value). Which method is best to facilitate this?:
1. Use the "instead of update on ViewName" syntax. Trigger on the
update of the view as a whole (ie any column triggers the one update).
or:
2. Use the "instead of update of ColumnName on ViewName" syntax in a
trigger for each column. Trigger on the update of each column/field
individually.
It seems to me that triggering on the view as a whole would
unnecessarily update multiple values/columns when only one is changed.
On the other hand, adding a trigger for each column seems overkill.
Which is the best way?
Below is the complete SQL of a simplified example, where I have an
Orders table and a Products table, (which lists the Products in each
order). I have an "Orders Calc" view which shows fields from the
Orders table, along with a sum() calculation of the Products in that
Order.
The result of the SQL by both methods is the same:
Testing method 1: Create a trigger for the view as a whole:
10001|Apple|22.0|1020.0|Cheque|2007-07-21|R1234|2007-07-21
10001|Apple|22.0|1020.0|Cheque|2007-07-22|R1234|2007-07-21
SQL error near line 85: You cannot change the Total, since it is
calculated.
Testing method 2: Create a trigger for the view per column:
10001|Apple|22.0|1020.0|Cheque|2007-07-21|R1234|2007-07-21
10001|Apple|22.0|1020.0|Cheque|2007-07-23|R1234|2007-07-21
SQL error near line 154: You cannot change the Total, since it is
calculated.
Any personal approaches or revelations welcome :-)
Thanks,
Tom
/*
Create the test tables and view.
*/
create table Orders
(
"Order ID" integer primary key autoincrement,
Supplier text, -- name of supplier
Delivery real, -- delivery cost in dollars
"Paid Method" text, -- method of payment, such as deposit, credit
card, cash
"Paid Date" date, -- date that payment was sent
Receipt text, -- payment receipt
Ordered date -- date that the order was sent
);
create table if not exists Products
(
"Order ID" integer, -- Orders foreign key
Code text, -- Product Code
Description text, -- Product Description
Buy real, -- Buy price I pay when ordering this item
Quantity integer -- Quantity of this product in this order
);
create view "Orders Calc"
as
select
Orders."Order ID" as "Order ID",
Supplier,
Delivery,
sum( Quantity * Buy ) + Delivery
as Total,
"Paid Method",
"Paid Date",
Receipt,
Ordered
from Orders
left join Products
on Orders."Order ID" = Products."Order ID"
group by Orders."Order ID";
/*
Insert Test data
*/
begin;
insert into Orders values ( 10001, 'Apple', 22.0, 'Cheque',
'2007-07-21', 'R1234', '2007-07-21');
insert into Products values ( 10001, 'IPH8GB', 'iPhone 8GB', 499.0, 2 );
commit;
/*
Method 1: Create a trigger for the view as a whole
*/
create trigger "Update Orders Calc"
instead of update on "Orders Calc"
for each row
begin
update Orders
set
Supplier = new.Supplier,
Delivery = new.Delivery,
"Paid Method" = new."Paid Method",
"Paid Date" = new."Paid Date",
Receipt = new.Receipt,
Ordered = new.Ordered
where "Order ID" = new."Order ID";
select
case
when old.Total != new.Total
then raise( abort, 'You cannot change the Total, since it is
calculated.')
end;
end;
/*
Test method 1
*/
begin;
select 'Testing method 1: Create a trigger for the view as a whole:';
select * from "Orders Calc";
update "Orders Calc"
set "Paid Date" = '2007-07-22' where "Order ID" = 10001;
select * from "Orders Calc";
update "Orders Calc"
set Total = 300.0 where "Order ID" = 10001;
rollback;
/*
Method 2: Create a trigger for the view per column
*/
drop trigger if exists "Update Orders Calc";
create trigger "Update Orders Calc Supplier"
instead of update of Supplier on "Orders Calc"
for each row
begin
update Orders set Supplier = new.Supplier where "Order ID" =
new."Order ID";
end;
create trigger "Update Orders Calc Delivery"
instead of update of Delivery on "Orders Calc"
for each row
begin
update Orders set Delivery = new.Delivery where "Order ID" =
new."Order ID";
end;
create trigger "Update Orders Calc Paid Method"
instead of update of "Paid Method" on "Orders Calc"
for each row
begin
update Orders set "Paid Method" = new."Paid Method" where "Order ID"
= new."Order ID";
end;
create trigger "Update Orders Calc Paid Date"
instead of update of "Paid Date" on "Orders Calc"
for each row
begin
update Orders set "Paid Date" = new."Paid Date" where "Order ID" =
new."Order ID";
end;
create trigger "Update Orders Calc Receipt"
instead of update of Receipt on "Orders Calc"
for each row
begin
update Orders set Receipt = new.Receipt where "Order ID" = new."Order
ID";
end;
create trigger "Update Orders Calc Ordered"
instead of update of Ordered on "Orders Calc"
for each row
begin
update Orders set Ordered = new.Ordered where "Order ID" = new."Order
ID";
end;
create trigger "Update Orders Calc Total"
instead of update of Total on "Orders Calc"
for each row
begin
select raise( abort, 'You cannot change the Total, since it is
calculated.');
end;
/*
Test method 2
*/
begin;
select 'Testing method 2: Create a trigger for the view per column:';
select * from "Orders Calc";
update "Orders Calc"
set "Paid Date" = '2007-07-23' where "Order ID" = 10001;
select * from "Orders Calc";
update "Orders Calc"
set Total = 400.0 where "Order ID" = 10001;
rollback;
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
