Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Location
    Southampton UK
    Posts
    15

    Unanswered: SQL Triggers - Problems

    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
    May 2004
    Location
    Southampton UK
    Posts
    15

    Problem solution

    Solution to the problem

    /* 1 Remove the old rubbish */
    DROP TABLE stock;
    DROP TABLE cust_sales;

    /* 2 Create the two new tables */
    CREATE TABLE stock(supp_code CHAR(4), part_code CHAR(10) PRIMARY KEY, description CHAR(40), unit_price

    DECIMAL(7,2), no_in_stock SMALLINT);

    CREATE TABLE cust_sales(sale_date DATE, cust_code CHAR(4), invoice_no INTEGER, part_code CHAR(10)

    REFERENCES stock, qty_sold SMALLINT);

    /* 3 Populate the stock table */
    INSERT INTO stock VALUES('CPC2', 'C-04-13567', '3.5in 1.44Mb Disk Drive', 5.67, 10);
    INSERT INTO stock VALUES('XMA4', 'X-03-63201', 'Canon BC01 Ink Cartriges', 12.21, 5);
    INSERT INTO stock VALUES('MPCM', '1234055774', 'Pentium 4 Computer as quoted', 340.50, 1);

    /* 4 Display the stock table */
    SELECT * FROM stock;

    /* 5 --------------------------- Stop for a tea break & clear the screen ------------------------------*/

    /* 6 Create the trigger */
    CREATE TRIGGER reset_stock AFTER INSERT ON cust_sales
    REFERENCING NEW AS sale
    FOR EACH ROW
    BEGIN
    UPDATE stock
    SET no_in_stock = no_in_stock - sale.qty_sold
    WHERE part_code = sale.part_code
    END;

    /* 7 Insert a sales record */
    INSERT INTO cust_sales VALUES(DATE('2004-06-06'),'DEA1', 43157, '1234055774', 1);

    /* 8 Display the cust_sales table */
    SELECT * FROM cust_sales;

    /* 9 Display the stock table */
    SELECT * FROM stock;

    For some reason or other the comments caused some problems, so I removed them and all was well. I carried out the nine actions separately.

Posting Permissions

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