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!

trigger changing price:

Discussion in 'General' started by monkey, Apr 25, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    We have next task:

    Customers buy books.
    If we delete one book, price customer pays for book has to change.

    Here's the code:
    Code (SQL):

    CREATE TABLE customer(
    id_customer INTEGER...
    name_customer varchar2(100)...
    whole_price-customer INTEGER...
    );

    CREATE TABLE custmer_book(
    id_customer_book INTEGER...
    fk_id_customer INTEGER...
    fk_id_book INTEGER...
    );

    CREATE TABLE book_a(
    id_book_a INTEGER...
    name_book_a varchar2(10)...
    price_book_a INTEGER...
    discount_book_a INTEGER...
    );

    CREATE TABLE book_b(
    id_book_b INTEGER...
    name_book_b varchar2(10)...
    price_book_b INTEGER...
    discount_book_b INTEGER...
    );

     
    Now, values:

    Code (SQL):

    INSERT INTO customer VALUES(1, 'c1', 600);  // 100+200+300= 600
    INSERT INTO customer VALUES(2, 'c2', 400);  // 100+300

    INSERT INTO customer_book VALUES(1, 1, 1);
    INSERT INTO customer_book VALUES(2, 1, 2);
    INSERT INTO customer_book VALUES(3, 1, 3);

    INSERT INTO customer_book VALUES(4, 1, 1);
    INSERT INTO customer_book VALUES(5, 1, 3);

    INSERT INTO book_a VALUES(1, 'b1', 200, 50);
    INSERT INTO book_a VALUES(1, 'b2', 400, 50);
    INSERT INTO book_a VALUES(1, 'b3', 600, 50);


     
     
  2. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Now, first step would be like:
    select discount_book_a into discount-book_b from book_a where id_book_b=:new.id_book_a;


    would it be right?
     
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Lets say I insert one row in book_a. (Then I probably have to insert a row in customer_book too).

    In that case, lets say both customers bought this new book, whole_price_customer would change its value.

    Trigger should price of this new book(price *discount (discount = rebate) add to whole_price_customer c1 and c2.
     
  4. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Code (SQL):
    CREATE TRIGGER t1 after inserting ON book_a
    but a question :

    it is not the same, after we insert fourth book id=4, , if this book is supposed to be bought by c1 or c2 or both.

    It means we should have insert on book_a AND customer_book

    can we write it somehow like this in trigger?

    We can make it easy for beginning suposing that both c1 and c2 woul buy inserted book.

    But, how to structure the tigger in that case?

    Please if someone knows a good link or anything?

    or at least appropriate key words...

    MANY THANKS!!!
     
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Code (SQL):

    CREATE TRIGGER t1 after INSERT ON book_b
    FOR each ROW

    DECLARE
    id_book_b NUMBER
    price_book_b NUMBER
    discount_book_b NUMBER
    final_price NUMBER
    together1 NUMBER
    together2 NUMBER




    BEGIN


    SELECT discount_book_a INTO discount_book_b FROM book_a WHERE id_book_a = :NEW.id_book_a

    SELECT price_book_a INTO price_book_b FROM book_a WHERE id_book_a= :NEW.id_book_a

    // did I WRITE correct till now?

    :NEW.discount_book_a := discount_book_b
    :NEW.price_book_a := price_book_b

    // here we obviouly put the VALUE FROM discount&price , which was still put TO discount_book_b AND price_book_b , respectively, put TO :NEW.discount_book_b, AND :NEW.price_book b respectively.

    are :NEW.discount_book_b AND :NEW.price_book_b VARIABLES THEN?

    :NEW.SUM:= :NEW.price_book_a+(:NEW.price_book_a * :NEW.discount_book_a/100);

    // this would probably be final_price


    SELECT whole_price_customer INTO together1 FROM customer WHERE id_customer= :NEW.id_customer

    // so, here we obviously GET a COLUMN :

    600
    400

    VALUE 600 IS IN the VARIABLE together1
    AND VALUE 400 IS ALSO IN variable together1, but ON another place OF the COLUMN...

    Did I understand it RIGHT?

    together1=together2+:NEW.SUM

    UPDATE customer SET whole_price_customer=together2 WHERE id_customer=:NEW.id_customer

    END t1;



     
    I do not see we have declared this :new.id_customer before.

    What is it exactly ment to be???

    PLEASE HELP SOMEONE IF POSSIBLE!!!
    SECOND WEEK WITH TRIGGER, SEEMS HARD FOR ME!!!
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have a long way to go before you are really ready to work on triggers.


    Triggers have two 'blocks', the OLD block (current data) and the NEW block (the updates to process or the data to insert). These 'blocks' are structured just like the table the trigger is created against. Every column in the table is found in both the OLD and NEW blocks. The : in front of each block name signals PL/SQL that it's a data block to read, so your UPDATE statement uses the current id_customer value from the list you supplied for the insert statement. I provided you a page of documentation links from the Oracle documentation on triggers -- you really need to read through those so you understand what triggers are, how they are written and what they can and cannot do.
     
    monkey likes this.