Re: SQL design pattern for a delta trigger?

Thanks Erik
>
> In a stored procedure you'd just execute the UPDATE
> and then check
> the FOUND variable to see if it found a row to
> update:
>
> UPDATE table_name SET foo='bar' WHERE id=5;
>
> IF NOT FOUND THEN
> INSERT INTO table_name (id, foo) VALUES (5, 'bar');
> END IF;
>
To be clear, if I understand you correctly, with your
example, if there is no record where id=5, nothing
happens except FOUND is set to false? Can I, then,
declare a variable prior to your update statement, and
then modify your update statement so that the value in
a particular field on the row where id=5 can be
captured? Bearing in mind this is to be in a row
level trigger after an insert into table_name,
something like:

DECLARE q DOUBLE;
UPDATE table_name
SET foo='bar',
q = table_name.quantity
WHERE id=5;

And then follow that with something like:
IF FOUND THEN
INSERT INTO another_table (baz,quantity)
VALUES (foo,q+NEW.quantity);
ELSE
INSERT INTO another_table (baz,quantity)
VALUES (foo,NEW.quantity);
END IF

Thanks again,

Ted