1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Function and Trigger code

Discussion in 'SQL PL/SQL' started by furqan5994, Jun 22, 2015.

  1. furqan5994

    furqan5994 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    hey everyone


    I am making a SQL database for a shop and i am have a lil problem in functions and triggers so i really hope someone here can help me out

    what i need is
    1. to create a function [GetDiscount] to get the DISCOUNT_RATE for a particular PROD_CAT from the Discount Table. along with setting an exception when DISCOUNT_RATE is not found for particular PROD_CAT
    2. to create a Trigger [TrgDisAmount] to read PROD_NAME and Price from Product Table and to calculate the DISCOUNT and AMOUNT of SALES Table fr evert new row with Serial and PCODE

    but for the TRIGGER

    i need to enter the values of PCODE and Serial only, rest should be entered automatically using the trigger


    i have attached pic along to show the tables needed
     

    Attached Files:

  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    hi.

    solutions to your problems are many.
    solutions may depend on the version of the database


    a simple example :

    Code (SQL):


    DROP TABLE complex_seq purge;
    CREATE TABLE complex_seq
    (
         seq_name varchar2(30 CHAR) NOT NULL PRIMARY KEY,
         seq_val  INTEGER
    );

    --
    INSERT INTO complex_seq  VALUES ('type1',1);
    INSERT INTO complex_seq  VALUES ('type2',1);
    INSERT INTO complex_seq  VALUES ('type3',1);
    commit;

    --
    CREATE OR REPLACE PROCEDURE gen_complex_seq (p_seq_name varchar2,p_val OUT varchar2)

    AS
        l_seq_val INT;
        pragma autonomous_transaction;
    BEGIN
        SELECT
              seq_val
        INTO
              l_seq_val      
        FROM
            complex_seq
        WHERE
            seq_name = p_seq_name
        FOR UPDATE;
       
        UPDATE
            complex_seq
        SET
            seq_val = seq_val +1
        WHERE
            seq_name = p_seq_name;
           
        p_val := substr(p_seq_name||'-'||to_char(l_seq_val),1,64);
           
        commit;            
                   
    END;        
    /

    DROP TABLE test_table;
    CREATE TABLE test_table
    (
        complex_seq varchar2(64 CHAR) NOT NULL PRIMARY KEY,
        seq_name    varchar2(32 CHAR) NOT NULL,
        msg         varchar2(100 CHAR)
    );

    --
    CREATE OR REPLACE TRIGGER tbi_test_table
    BEFORE INSERT ON test_table
    FOR each ROW
    DECLARE
       l_seq_val varchar2(64 CHAR);

    BEGIN
        IF :NEW.complex_seq IS NULL THEN

            gen_complex_seq(:NEW.seq_name,:NEW.complex_seq);

        END IF;

    END;
    /

    INSERT INTO  test_table(seq_name,msg)
    SELECT
      csq.seq_name,
      z.column_value
    FROM
      complex_seq csq,TABLE(sys.odcinumberlist(1,2,3,4,5,6,7)) z;
    commit;

    SELECT * FROM test_table ORDER BY 1;  
    SELECT * FROM complex_seq;


     
     
  3. furqan5994

    furqan5994 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Respected Sir,

    Is it possible for you to give me an answer based on the table, trigger and function names as i mentioned in the question. it would be great help
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    this simple example can be easily adapted to your task.....
    you begin to do ...

    if you have questions, then ask ...
     
  5. furqan5994

    furqan5994 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    is it possible that i can email you or something if i have any issues
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    the best questions to ask here....
    but you can ask by e-mail...
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "I am making a SQL database for a shop ..."


    Are you telling us this is a SQL Server database? If so this is an ORACLE Forum and you won't find any solutions to your problems here. If this is Oracle then report the version number you are using so we can know HOW to answer your question.
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    OP is not making a database for a shop. He is shopping for answers to his homework. This 'problem' can be found elsewhere via a Google search.
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi furqan,

    Try to post ur work U did for findin the solutn.. Let me help U out dis tym.

    1. GetDiscount - Function...

    Code (SQL):


    CREATE OR REPLACE FUNCTION GetDiscount (i_PROD_CAT IN varchar2)
    RETURN varchar2
    AS
    v_discount_rate discount.discount_rate%TYPE;
    BEGIN
    SELECT DISCOUNT_RATE INTO V_DISCOUNT_RATE FROM DISCOUNT
    WHERE prod_cat=i_prod_cat AND rownum=1;
    RETURN V_DISCOUNT_RATE;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'DISCOUNT_RATE NOT FOUND';
    END;

     
     
  10. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    2. And The trigger...



    Code (SQL):


    CREATE OR REPLACE TRIGGER SALESTRG
    BEFORE INSERT ON SALES
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

    DECLARE
    v_product_name product.PROD_NAME%TYPE;
    v_price product.price%TYPE;
    v_discount discount.discount_rate%TYPE;

    BEGIN

    dbms_output.put_line('VKY');

    SELECT PROD_NAME INTO v_product_name
    FROM product WHERE p_code=:NEW.p_code;

    SELECT price INTO v_price
    FROM product WHERE p_code=:NEW.p_code;

    DBMS_OUTPUT.PUT_LINE('Befor Discount_rate');

    SELECT discount_rate INTO v_discount
    FROM DISCOUNT WHERE PROD_CAT IN (SELECT PROD_CAT
    FROM PRODUCT WHERE P_CODE=:NEW.P_CODE) AND ROWNUM=1;

    IF :NEW.PROD_NAME IS NULL THEN

    :NEW.prod_name := v_product_name;

    END IF;

    IF :NEW.DISCOUNT IS NULL THEN

      :NEW.DISCOUNT := v_discount;

    END IF;

    IF :NEW.AMOUNT IS NULL THEN

      :NEW.AMOUNT := (V_PRICE*V_DISCOUNT/100);
     
    END IF;

    END;

     
     
  11. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    The trigger could be made compact further like..

    Code (SQL):


    CREATE OR REPLACE TRIGGER SALESTRG
    BEFORE INSERT ON SALES
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
    DECLARE
    v_product_name product.PROD_NAME%TYPE;
    v_price product.price%TYPE;
    v_discount discount.discount_rate%TYPE;

    BEGIN

    dbms_output.put_line('VKY');

    SELECT PROD_NAME INTO v_product_name
    FROM product WHERE p_code=:NEW.p_code;

    SELECT price INTO v_price
    FROM product WHERE p_code=:NEW.p_code;

    DBMS_OUTPUT.PUT_LINE('Befor Discount_rate');

    SELECT discount_rate INTO v_discount
    FROM DISCOUNT WHERE PROD_CAT IN (SELECT PROD_CAT
    FROM PRODUCT WHERE P_CODE=:NEW.P_CODE) AND ROWNUM=1;

    IF :NEW.PROD_NAME IS NULL OR :NEW.DISCOUNT IS NULL OR :NEW.AMOUNT IS NULL THEN

    :NEW.PROD_NAME := V_PRODUCT_NAME;

    :NEW.DISCOUNT := V_DISCOUNT;

    :NEW.AMOUNT := (V_PRICE*V_DISCOUNT/100);

    END IF;

    END;