One must update the auditstamp whenever an update/insert took place.
The other should set inv.total = sum(itm.amount) where itm.idinv = inv.idinv whenever an itm gets added / updated.
The first trigger gives me an SQL error and I cant figure out why. The second executes but doesnt do anything when I enter:
insert into inv (total) values (0.00); select * from inv; |1|0.00| insert into itm (idinv,row,amount) values (1,0,99.99); select * from inv; |1|0.00|
-- it should be |1|99.99|
An inv and itm has a one to many relationship.
Any advice appretiated.
Thanks T
------------------------------------------------------------------------------------------
DROP TABLE inv; CREATE TABLE inv ( idinv integer primary key not null, total numeric(10,2) default 0.00, audit date -- Timestamp last modified );
DROP TABLE itm; CREATE TABLE itm ( idinv integer, -- primary key part1, fk inv.id row integer, -- primary key part2, (ROW AS APPEARING IN THE INVOICE) amount numeric(10,2), audit date, -- timestamp last modified primary key (idinv, row) );
-- Trigger 1
-- update the inv.audit with current datetime() whenever record inserted or updated --
CREATE TRIGGER upd_audit ON UPDATE, INSERT OF inv
BEGIN
UPDATE inv set audit = select datetime('now');
END
-- Trigger 2
-- add all itm.amount where itm.idinv = inv.idinv and update inv.total --
CREATE TRIGGER tally_inv_total ON UPDATE, INSERT, DELETE OF itm
BEGIN
UPDATE inv set total = select sum(itm.amount) where itm.idinv = inv.idinv;
END
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
