[geraldisaacs]

I have problems creating a SQL Trigger

Two tables are concerned
1. named stock which records details of items a retailer holds for re-sale
2. named cust_sales which records details of sales made

The stock table includes the fields (no_in_stock INT) and (part_code CHAR(10))
The cust_sales table includes the fields (qty_sold INT) and (part_code CHAR(10) REFERENCES stock)

When a sale is recorded in the cust_sales table for an item identified by part_code I want the no_in_stock field in the stock table to be automatically reduced by the value of the qty_sold field from the cust_sales table.

Here is my attempt thus far
CREATE TRIGGER reset_stock AFTER INSERT ON cust_sales
ON EACH ROW
BEGIN
UPDATE stock
stock.no_in_stock = stock.no_in_stock - cust_sales.qty_sold
WHERE stock.part_code = cust_sales.part_code
END

When I attempt to append a record to the cust_sales table using

INSERT INTO cust_sales VALUES(DATE('2004-06-06'),'DEA1', 43157, '1234055774', 1);
I receive an error

Line 1
SQLSTATE = 50002
[Sybase][ODBC Driver] Table or view not found: correlation name 'cust_sales' not found

cust_sales table structure
=====================
sale_date DATE
cust_code CHAR(4)
invoice_no INT
part_code CHAR(10) REFERENCES stock
qty_sold INT

stock table structure
=====================
supp_code CHAR(4)
part_code CHAR(10)
description CHAR(40)
unit_price DECIMAL(7,2)
no_in_stock INT

I have recently started studying SQL and I would be grateful if someone could help me with this problem

[LKBrwn_DBA]

CREATE TRIGGER reset_stock AFTER INSERT ON cust_sales
ON EACH ROW
BEGIN
UPDATE stock
stock.no_in_stock = stock.no_in_stock - cust_sales.qty_sold
WHERE stock.part_code = cust_sales.part_code
END

Not very familiar with SYBASE, but I think you may have to code it like this:

CREATE TRIGGER reset_stock AFTER INSERT ON cust_sales 
      ON EACH ROW
BEGIN
  UPDATE stock
       SET stock.no_in_stock = stock.no_in_stock - inserted.qty_sold
   WHERE stock.part_code   = inserted.part_code
END