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