Results 1 to 2 of 2

Thread: SQL Triggers

  1. #1
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15

    Unanswered: SQL Triggers

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    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:

    Code:
    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

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •