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!

Procedure, Function or Trigger?

Discussion in 'SQL PL/SQL' started by olddog, Oct 12, 2010.

  1. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hi All
    I,m trying to solve the problem below but not sure if I'm heading in the right direction, can anyone help?
    Problem:
    When a new movie is added to the database it needs to automatically set the TotalCopies to 10 if the movie was added in the first six months of the year else it should be 15.
    I wasn't sure whether to use a procedure, function or trigger anyway i went for trigger.
    The following code throws an Error(5,54): PL/SQL: ORA-00933: SQL command not properly ended.

    CREATE OR REPLACE TRIGGER movie_Qty
    AFTER INSERT ON Movie
    DECLARE Movie_PurchaseDate DATE;
    BEGIN
    IF Movie_PurchaseDate BETWEEN to_date ('01/jan/2010', 'dd/mon/yyyy')
    AND to_date ('30/jun/2010', 'dd/mon/yyyy')
    THEN INSERT INTO Movie.TotalCopies VALUES ('10')
    ELSE
    INSERT INTO Movie.TotalCopies VALUES ('15');
    END IF;
    END;
    /

    Thanks olddog
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    What is the value of Movie_PurchaseDate and what is the source for Movie_PurchaseDate to get data.
    Give some clear explanation about this scenario.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're missing a necessary semicolon:

    Code (SQL):
     
    CREATE OR REPLACE TRIGGER movie_Qty
    AFTER INSERT ON Movie
    DECLARE Movie_PurchaseDate DATE;
    BEGIN
    IF Movie_PurchaseDate BETWEEN to_date ('01/jan/2010', 'dd/mon/yyyy')
    AND to_date ('30/jun/2010', 'dd/mon/yyyy')
    THEN INSERT INTO Movie.TotalCopies VALUES ('10'); <---- Here
    ELSE
    INSERT INTO Movie.TotalCopies VALUES ('15');
    END IF;
    END;
    /
     
     
     
  4. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for the help.
    After looking at the problem I figured it would best be handled by a FUNCTION which i successfully compiled:

    CREATE OR REPLACE FUNCTION new_movie
    (Purchase_Date IN DATE)
    RETURN NUMBER
    IS
    Total_Copies NUMBER;
    BEGIN
    IF PURCHASE_DATE BETWEEN to_date ('01/jan/2010', 'dd/mon/yyyy')
    AND to_date ('30/jun/2010', 'dd/mon/yyyy') THEN
    Total_Copies :=(10);
    ELSE
    Total_Copies :=(15);
    END IF;
    RETURN Total_Copies;
    END;
    /

    Now I,m stuck, how do I insert the Values of Total_Copies (either 10 or 15 based on when the movie was purchased) into the Movies.TotalCopies table? and am I heading in the right direction.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Write a trigger , take some variable in trigger, call the function in trigger apparently., assign the value to variable returned by function .
    i.e. v_var := new_movie:)NEW.Movie_PurchaseDate)
    then insert the value into table using insert statement.
    i.e. INSERT INTO Movie.TotalCopies VALUES (v_var);
    try this.
     
    Sadik likes this.
  6. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    thanks Kiran ill try that :)
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You could do this:

    insert into Movies.TotalCopies select new_movie(movie_purchasedate) from movie;

    But how are you identifying WHICH movie has which total copies value? One would expect that Movies.TotalCopies would have at least two columns, Movie_Id and TotalCopies. As an example, using your function:

    Code (SQL):
     
    SQL> CREATE OR REPLACE FUNCTION new_movie
    2 (Purchase_Date IN DATE)
    3 RETURN NUMBER
    4 IS
    5 Total_Copies NUMBER;
    6 BEGIN
    7 IF PURCHASE_DATE BETWEEN to_date ('01/jan/2010', 'dd/mon/yyyy')
    8 AND to_date ('30/jun/2010', 'dd/mon/yyyy') THEN
    9 Total_Copies :=10;
    10 ELSE
    11 Total_Copies :=15;
    12 END IF;
    13 RETURN Total_Copies;
    14 END;
    15 /
    FUNCTION created.
    SQL>
    SQL> CREATE TABLE movie(
    2 movie_id NUMBER NOT NULL,
    3 movie_title varchar2(80) NOT NULL,
    4 lead_actor varchar2(40),
    5 studio varchar2(80),
    6 movie_purchasedate DATE NOT NULL,
    7 CONSTRAINT movie_pk PRIMARY KEY (movie_id)
    8 );
    TABLE created.
    SQL>
    SQL> CREATE TABLE totalcopies(
    2 movie_id NUMBER NOT NULL,
    3 totalcopies NUMBER NOT NULL,
    4 CONSTRAINT totalcopies_fk FOREIGN KEY(movie_id) REFERENCES movie
    5 );
    TABLE created.
    SQL>
    SQL> CREATE SEQUENCE movie_seq
    2 START WITH 1 INCREMENT BY 1 nomaxvalue nocycle ORDER;
    SEQUENCE created.
    SQL>
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'Ben Hur', NULL, NULL, to_date('03/08/2010','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'Lawrence of Arabia', NULL, NULL, to_date('07/08/2010','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'Alvin and The Chipmunks', NULL, NULL, to_date('03/18/2010','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'The Notebook', NULL, NULL, to_date('06/07/2010','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'Bolt', NULL, NULL, to_date('05/08/2010','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'Three Days of the Condor', NULL, NULL, to_date('03/08/2001','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL> INSERT
    2 INTO movie
    3 SELECT movie_seq.NEXTVAL, 'The Dark Knight', NULL, NULL, to_date('03/08/2010','mm/dd/rrrr') FROM dual;
    1 ROW created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> INSERT INTO totalcopies
    2 (movie_id, totalcopies)
    3 SELECT movie_id, new_movie(movie_purchasedate) FROM movie;
    7 ROWS created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT m.movie_id, m.movie_title, m.movie_purchasedate, c.totalcopies
    2 FROM movie m, totalcopies c
    3 WHERE c.movie_id = m.movie_id;
    MOVIE_ID MOVIE_TITLE MOVIE_PURC TOTALCOPIES
    ---------- --------------------------------------------- ---------- -----------
    1 Ben Hur 03/08/2010 10
    2 Lawrence OF Arabia 07/08/2010 15
    3 Alvin AND The Chipmunks 03/18/2010 10
    4 The Notebook 06/07/2010 10
    5 Bolt 05/08/2010 10
    6 Three Days OF the Condor 03/08/2001 15
    7 The Dark Knight 03/08/2010 10
    7 ROWS selected.
    SQL>
     
     
     
    olddog likes this.
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why? The function can be used in a select statement to populate the column value without a trigger. I don't understand why this has to become so complicated when it's a fairly simple task.
     
  9. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Nice script, thanks David works well, but Kiran maybe right, the function works well with INSERT INTO totalcopies (movie_id, totalcopies) SELECT movie_id, new_movie(movie_purchasedate) FROM movie;
    but if you were to Insert a new movie(insert into movie VALUES(.......... wouldn't you need some kind of trigger to run the function i.e.
    create or replace
    TRIGGER total_copies
    AFTER insert ON movie
    BEGIN
    (not sure from here, still reading up on it...lol)
    (maybe) execute function new_movie;
    END;
     
  10. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    OK, I cant find any info on triggering a function so I wrote a trigger script instead, can someone tell me why it is ignoring the first If statement ?
    I tried inserting a new movie into movie with the purchasedate '15-feb-10', the trigger fired and inserted into the totalcopies table the Id and 15, it should have inserted Id and 10, the logic in the script looks fine to me what have I overlooked?
    create or replace
    TRIGGER "TOTAL_COPIES"
    AFTER INSERT OR UPDATE ON Movie
    DECLARE
    purchasedate DATE;
    BEGIN
    DELETE TOTALCOPIES;
    IF PurchaseDate BETWEEN to_date('01/jan/10', 'dd/mon/yyyy')
    AND to_date('30/jun/10', 'dd/mon/yyyy')THEN
    INSERT INTO totalcopies (SELECT Id,10 FROM movie);
    ELSE
    INSERT INTO TotalCopies (SELECT Id,15 FROM movie);
    END IF;

    END;

    Thanks for the help :)
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Changes made to posted code. Modified code shown below:

    Code (SQL):
    CREATE OR REPLACE TRIGGER "TOTAL_COPIES"
    AFTER INSERT OR UPDATE ON Movie
    DECLARE
    purchasedate DATE;
    BEGIN
    DELETE FROM TOTALCOPIES WHERE Id = :NEW.Id;
    IF :NEW.movie_PurchaseDate BETWEEN to_date('01/jan/10', 'dd/mon/yy')
    AND to_date('30/jun/10', 'dd/mon/yy')THEN
    INSERT INTO totalcopies VALUES(:NEW.Id,10 );
    ELSE
    INSERT INTO TotalCopies VALUES(:NEW.Id,15);
    END IF;
     
    END;
    /
     
    olddog likes this.
  12. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for the help David, I tried that script but threw Error: ORA-04082: NEW or OLD references not allowed in table level triggers
    This script is close, I can feel it in my waters lol. ;)
     
  13. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    ATM its inserting everything as 15 regardless of the date
     
  14. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for the help David problem solved with some tweeks to your script:
    create or replace
    TRIGGER "TOTAL_COPIES"
    AFTER INSERT ON Movie for each row

    BEGIN

    IF :new.PurchaseDate BETWEEN to_date('01/jan/10', 'dd/mon/yy') AND to_date('30/jun/10', 'dd/mon/yy')
    THEN INSERT INTO totalcopies values:)new.Id,10 );
    ELSE INSERT INTO TotalCopies values:)new.Id,15);
    END IF;

    Cheers mate

    END trigger;