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: SELECT FROM TABLEA TO TABLEB

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

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    HELLO!
    I have table bookA(
    id_book_a ...
    price_book_a...
    discount_book_a
    );


    and I want to send the data to table bookB
    where discount_book_b would be a variable that would have the (new, but the same!) value of discount_book_a

    so, do I write then:

    select discount_book_a into discount_book_b from book_a where id_book_a=:new.id_book_b?

    How do I say that I want discount_book_b values to be in table named right book_b???

    Searching for syntas, but can't find...


    many thanks!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    See documentation:

    Insert

    Code (SQL):


    INSERT INTO TableB    SELECT *    FROM TableA    WHERE id_b = :NEW.id_a;

     
    For what to do these actions in the trigger?
    The trigger are intended for other tasks.
    For example - expanded rules of integrity
     
    monkey likes this.
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    If I had both statements:
    Code (SQL):
    SELECT discount_book_a INTO discount_book_b FROM book_a WHERE id_a=:NEW.id_b?
    Code (SQL):
    INSERT INTO TableB    SELECT * FROM TableA    WHERE id_b = :NEW.id_a;
    Would it go together in one trigger?

    Many thanks!!!
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    1) In triggers SELECT can't use to the changeable table since you receive MUTATION error.
    2) select discount_book_a into discount_book_b from book_a where id_book_a=:new.id_book_b? -- For what it to you in the trigger ?

    ...

    declare
    your_variable your_table.your_column%type;
    begin
    your_variable := :new.your_column;
    ...
    end;
     
    monkey likes this.
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I the declaration, is it possible to have just

    variable type without table in the middle?
    Have reason to ask...(we are ment to find the holes in the task alone, using just net , and I am not well doing it!)

    DOES IT MEAN, when we HAVE to decalre the name of the table(that table wehere we are sending :new variables to), together with declaring names of :new variables???

    That is one of the points in the tast we have to find out alone!!

    Many thanks!!!
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Something is necessary similar?

    Code (SQL):

    CREATE TABLE book_a
    (
       id_book          INT PRIMARY KEY,
       price_book       NUMBER,
       discount_book    NUMBER
    );
    CREATE TABLE book_h
    --
    (
       id_book          INT,
       price_book       NUMBER,
       discount_book    NUMBER,
       book_disc        DATE,            -- hist date
       action           CHAR(1)
    );
    CREATE OR REPLACE TRIGGER book_a_t_aiu
    after INSERT OR UPDATE   ON book_a
    FOR each ROW
        DECLARE
              l_action book_h.action%TYPE;
    BEGIN
        l_action := CASE WHEN inserting  THEN 'I' ELSE 'U' END ;

            INSERT INTO book_h VALUES (:NEW.id_book,:NEW.price_book,:NEW.discount_book,sysdate,l_action);
       
    END;
    /

    INSERT INTO book_a VALUES (1,18.9,NULL);
    INSERT INTO book_a VALUES (2,100,NULL);
    INSERT INTO book_a VALUES (3,5,NULL);
    INSERT INTO book_a VALUES (4,777,17.77);
    UPDATE book_a SET discount_book = 3.99 WHERE id_book = 2;
    commit;
    SELECT * FROM book_h;

    SQL>

    TABLE created
     
    TABLE created
     
    TRIGGER created
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW updated
     
    Commit complete
     
                                    ID_BOOK PRICE_BOOK DISCOUNT_BOOK BOOK_DISC   ACTION
    --------------------------------------- ---------- ------------- ----------- ------
                                          4        777         17.77 25.04.2014  I
                                          2        100          3.99 25.04.2014  U
     
    SQL>


     
     
    monkey likes this.
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):

    SQL>
    SQL> --
    SQL> -- Taking Sergey's example a bit further
    SQL> --
    SQL> -- Create the book inventory tables
    SQL> --
    SQL> CREATE TABLE book_a
      2  (
      3     id_book          INT PRIMARY KEY,
      4     price_book       NUMBER,
      5     discount_book    NUMBER
      6  );


    TABLE created.


    SQL>
    SQL> CREATE TABLE book_h
      2  --
      3  (
      4     id_book          INT,
      5     price_book       NUMBER,
      6     discount_book    NUMBER,
      7     book_disc        DATE, -- hist date
      8     action           CHAR(1)
      9  );


    TABLE created.


    SQL>
    SQL> --
    SQL> -- Create trigger to populate the discount table
    SQL> --
    SQL> -- Modified the trigger to set the discount date to NULL
    SQL> -- if no discounted price is available
    SQL> --
    SQL> -- updates existing records in the discount book table
    SQL> -- on update
    SQL> --
    SQL> CREATE OR REPLACE TRIGGER book_a_t_aiu
      2  after INSERT OR UPDATE ON book_a
      3  FOR each ROW
      4      DECLARE
      5            l_action book_h.action%TYPE;
      6  BEGIN
      7      l_action := CASE WHEN inserting  THEN 'I' ELSE 'U' END ;
      8
      9      IF inserting THEN
     10          IF :NEW.discount_book IS NOT NULL THEN
     11                  INSERT INTO book_h VALUES (:NEW.id_book,:NEW.price_book,:NEW.discount_book,sysdate,l_action);
     12          ELSE
     13                  INSERT INTO book_h VALUES (:NEW.id_book,:NEW.price_book,:NEW.discount_book,NULL,l_action);
     14          END IF;
     15      elsif updating THEN
     16          IF :NEW.discount_book IS NOT NULL THEN
     17                  UPDATE book_h SET price_book = :NEW.price_book, discount_book=:NEW.discount_book, book_disc=sysdate, action=l_action
     18                  WHERE id_book=:NEW.id_book;
     19          ELSE
     20                  UPDATE book_h SET price_book = :NEW.price_book, discount_book=:NEW.discount_book, book_disc=NULL, action=l_action
     21                  WHERE id_book=:NEW.id_book;
     22          END IF;
     23      END IF;
     24  END;
     25  /


    TRIGGER created.


    SQL>
    SQL> SHOW errors TRIGGER book_a_t_aiu

    No errors.

    SQL>
    SQL> --
    SQL> -- Insert some data into the master book table
    SQL> --
    SQL> INSERT INTO book_a VALUES (1,18.9,NULL);


    1 ROW created.


    SQL> INSERT INTO book_a VALUES (2,100,NULL);


    1 ROW created.


    SQL> INSERT INTO book_a VALUES (3,5,NULL);


    1 ROW created.


    SQL> INSERT INTO book_a VALUES (4,777,17.77);


    1 ROW created.


    SQL>
    SQL> --
    SQL> -- Verify the trigger wrote the proper records to the
    SQL> -- discount book table
    SQL> --
    SQL> SELECT * FROM book_h;


       ID_BOOK PRICE_BOOK DISCOUNT_BOOK BOOK_DISC A
    ---------- ---------- ------------- --------- -
             1       18.9                         I
             2        100                         I
             3          5                         I
             4        777         17.77 25-APR-14 I


    SQL>
    SQL> --
    SQL> -- Update a book to give a discount
    SQL> --
    SQL> UPDATE book_a SET discount_book = 3.99 WHERE id_book = 2;


    1 ROW updated.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Verify the trigger wrote the proper records to the
    SQL> -- discount book table
    SQL> --
    SQL> SELECT * FROM book_h;


       ID_BOOK PRICE_BOOK DISCOUNT_BOOK BOOK_DISC A
    ---------- ---------- ------------- --------- -
             1       18.9                         I
             2        100          3.99 25-APR-14 U
             3          5                         I
             4        777         17.77 25-APR-14 I


    SQL>
    SQL> --
    SQL> -- Update the NULL discounts
    SQL> --
    SQL> UPDATE book_a SET discount_book = 0.4*price_book WHERE discount_book IS NULL;


    2 ROWS updated.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Verify the trigger wrote the proper records to the
    SQL> -- discount book table
    SQL> --
    SQL> SELECT * FROM book_h;


       ID_BOOK PRICE_BOOK DISCOUNT_BOOK BOOK_DISC A
    ---------- ---------- ------------- --------- -
             1       18.9          7.56 25-APR-14 U
             2        100          3.99 25-APR-14 U
             3          5             2 25-APR-14 U
             4        777         17.77 25-APR-14 I


    SQL>
    SQL> --
    SQL> -- Update the discounts to NULL for book_id values 1 through 3
    SQL> --
    SQL> UPDATE book_a SET discount_book = NULL WHERE id_book IN (1,2,3);


    3 ROWS updated.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Verify the trigger wrote the proper records to the
    SQL> -- discount book table
    SQL> --
    SQL> SELECT * FROM book_h;


       ID_BOOK PRICE_BOOK DISCOUNT_BOOK BOOK_DISC A
    ---------- ---------- ------------- --------- -
             1       18.9                         U
             2        100                         U
             3          5                         U
             4        777         17.77 25-APR-14 I


    SQL>
     

    You can do inserts, updates and deletes in a standard trigger, just not against the table the trigger is created against. There is a new compound trigger but that's a subject for a different thread. Get a good concept of standard triggers first before exploring any new territory.
     
  8. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Many thanks!
    What mean "I" and "U"?

    I ment discount as rebate. If price is 100 € and rebate 50 (%)then final price is 50°€

    The problem we have to solve here is how to place :new values to (virtual?) table book_b...
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Sergey and I have both shown you how to do that.


    "I" == Insert
    "U" == Update


    It's a tracking column so you know what the last transaction type was against each row.