SQL : Triggers:

[TokenHerbz]

I'm having a hard time getting my trigger to work, so i would like someone to display the syntax for one correctly

Here are my current attempts:

I will display the procedure and function to since the triggers are based of the updating of the orders table.

code:

SET SERVEROUTPUT ON; --(Stored Function) CREATE OR REPLACE FUNCTION CalcValue (    iMfr IN CHAR,    iProduct IN CHAR,    iQuantity IN NUMBER ) RETURN NUMBER AS    wCalcValue NUMBER(10,2); BEGIN    SELECT (Price * iQuantity)    INTO wCalcValue    FROM Products    WHERE iMfr = Mfr      AND iProduct = Product;    RETURN wCalcValue; END; / SHOW ERRORS; -- (Stored Procedure) CREATE OR REPLACE PROCEDURE AddOrder (   iCustomer IN NUMBER,   iRep IN NUMBER,   iMfr IN CHAR,   iProduct IN CHAR,   iQuantity IN NUMBER ) AS    wAmountOwed NUMBER(10,2);    wOrderNumber NUMBER(10); BEGIN    SELECT MAX(OrderNum)+1    INTO wOrderNumber    FROM Orders;        wAmountOwed := CalcValue(iMfr,iProduct,iQuantity);        INSERT INTO Orders(OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount)    VALUES (wOrderNumber, CURRENT_DATE, iCustomer, iRep, iMfr, iProduct, iQuantity, wAmountOwed); END; / SHOW ERRORS;     --(Triggers) CREATE OR REPLACE TRIGGER ProductName   AFTER INSERT ON Orders   FOR EACH ROW DECLARE BEGIN   UPDATE Products   SET QtyOnHand = QtyOnHand - (      SELECT Qty      FROM Orders      WHERE OrderNum = MAX(OrderNum)      );    WHERE Mfr = (      SELECT Mfr      FROM Orders      WHERE OrderNum = MAX(OrderNum)      );    AND Product = (      SELECT Product      FROM Orders      WHERE OrderNum = MAX(OrderNum)    ); END; / SHOW ERRORS; CREATE OR REPLACE TRIGGER SalesRepMaint    AFTER INSERT ON Orders    FOR EACH ROW DECLARE BEGIN    UPDATE SalesReps    SET Sales = (       SELECT Amount       FROM Orders       WHERE OrderNum = MAX(OrderNum)       );    WHERE SalesRep = (       SELECT Rep       FROM Orders       WHERE OrderNum = MAX(OrderNum)     ); END; / SHOW ERRORS; CREATE OR REPLACE TRIGGER OfficesMaint    AFTER INSERT ON Orders    FOR EACH ROW DECLARE BEGIN END; \ SHOW ERRORS;         --(Triggers) CREATE OR REPLACE TRIGGER ProductName   AFTER INSERT ON Orders   FOR EACH ROW DECLARE   wQuantity NUMBER(5); BEGIN  --  SELECT Qty   -- INTO wQuantity    --FROM Orders    --WHERE OrderNum = MAX(OrderNum);        wQuantity := (      SELECT Qty      FROM Orders      WHERE OrderNum = MAX(OrderNum)    );        UPDATE Products    SET QtyOnHand = wQuantity;       END; / SHOW ERRORS;

i can supply an ERD or some population data if someone required it... thx..

[Tony]

TokenHerbz @ Sun Mar 25, 2012 8:35 pm wrote:

i would like someone to display the syntax for one correctly

Examples in the documentation for your SQL engine of choice is probably your best bet. E.g., if you're using MySQL 5 -- http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html