I am experimenting with creating a trigger to facilitate tracking
edits. There is an investments table, with a descriptor and an
autoincremented index, a holdings table, and a transactions table (NOT
SQL transactions, but buy or sell orders for investments). To keep
things simple, I stripped away anything to do with portfolio IDs
(which would require a little more in the WHERE clauses when added
back in).

The aim is to have a trigger execute after an insert into the
transactions table, and this trigger creates a record for the
investment, if the portfolio doesn't presently contain the investment,
and then if the order is to buy, increment the quanity of the
investment represented in the holdings table buy the quantity in the
transaction and if the order is to sell, decrement it by the quanity
represented in the order.

What I have tried (in a file called transaction_trigger_test.sql) is
appended below.

One of the requirements is that a given quantity in the holdings table
has a valid time (the time between orders to change the investment).
So, with a start and end date, and NO deletions from the table, we can
reconstruct the history of the holdings, and know that the presently
open positions are those with a null value for the end date.

There is eventually to be another trigger, that hapens before an
insert, and that will be executed only for records in which the action
is to close a position (in which case the quantity in the newly
inserted record in the transaction table is set to the quantity in the
open position in that investment, and the record of that position in
the holdings table would have the end date set to the transaction
date. In such a case, no further processing in any trigger is
required.

The problem is that MySQL tells me there is a syntax error near where
the first insert ends and the update statement begins. But it doesn't
say what the syntax error is. When I check the manual, I don't see
any obvious differences between what the manual says and what I wrote.

Can you help me spot the errors in this script?

Is there a better way to do this?

If worse comes to worse, I'll do it in the application's client layer
(which would take but a few hours to implement), but this seems like
something that ought to live entirely within the database.

Any help would be appreciated.

Thanks

Ted
============================================
sorry about the format, something happens when plain text is copied
from my sql script file and it is pasted into this web control for
posting to this forum
============================================
USE test;

delimiter //

DROP TRIGGER IF EXISTS `test_portfolio_handler`;
CREATE TRIGGER `test_portfolio_handler` AFTER INSERT
ON t1_transactions
FOR EACH ROW
BEGIN

IF (NEW.act = "BUY") OR (NEW.act = "SELL") THEN
INSERT INTO holdings (i_no,quantity,start_date,end_date)
VALUES (NEW.i_no,0,NEW.tdate,null)
WHERE NEW.i_no = holdings.i_no AND holdings.end_date IS
NULL;

UPDATE holdings SET holdings.end_date = NEW.tdate
WHERE holdings.i_no = NEW.i_no AND holdings.end_date IS NULL;

INSERT INTO holdings (i_no,quantity,start_date,end_date)
VALUES (NEW.i_no,
NEW.quantity + (SELECT quantity FROM holdings
WHERE i_no = NEW.i_no
AND end_date IS NULL),
NEW.tdate,
null)
WHERE NEW.kind = "BUY";

INSERT INTO holdings (i_no,quantity,start_date,end_date)
VALUES (NEW.i_no,
0 - NEW.quantity + (SELECT quantity FROM holdings

WHERE i_no = NEW.i_no
AND end_date IS NULL),
NEW.tdate,
null)
WHERE NEW.kind = "SELL";
END
//


delimiter ;

Ted wrote:
> When I check the manual, I don't see
> any obvious differences between what the manual says and what I wrote.

You don't!!!

Please point me to the page in the manual where it shows that the syntax for
an INSERT contains a WHERE clause?