Forum Stats

  • 3,816,531 Users
  • 2,259,202 Discussions
  • 7,893,505 Comments

Discussions

Oracle Warning: Trigger Created With Compilation Errors

1002068
1002068 Member Posts: 12
edited Apr 9, 2013 4:28PM in SQL & PL/SQL
I don't know what I am doing wrong here, I looked at the error, but I can't seem to figure it out.
SQL> CREATE OR REPLACE TRIGGER TRANS_AskingPriceIniValue
2 AFTER INSERT ON Transactions
3 DECLARE
4 rowcount INTEGER;
5 NetProfit NUMBER;
6 avgNetProfit NUMBER;
7 BEGIN
8 SELECT Count(*) INTO rowcount
9 FROM Transactions T
10 WHERE new.WorkID = T.WorkID;
11 IF rowcount := 1 THEN
12 new.AskingPrice = 2 * new.AquisitionPrice;
13 ELSE IF
14 rowcount > 1 THEN SELECT Sum(NetPRofit) INTO NetProfit
15 FROM ArtistWorkNetView AW
16 WHERE AW.WorkID = new.WorkID
17 GROUP BY AW.WorkID;
18 avgNetProfit = NetProfit / (rowcount - 1);
19 IF avgNetProfit > 2 * new.AcquisitionPrice THEN
20 new.AskingPrice = avgNetProfit;
21 ELSE
22 new.AskingPrice = 2 * new.AcquistionPrice;
23 END IF;
24 END IF;
25 END IF;
26 END;
27 /

Warning: Trigger created with compilation errors.

SQL> SHOW ERRORS
Errors for TRIGGER TRANS_ASKINGPRICEINIVALUE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/13 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
. ( * @ % & = - + < / > at in is mod not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,921 Red Diamond
    edited Apr 9, 2013 3:15PM
    Hi,

    Welcome to the forum!
    999065 wrote:
    I don't know what I am doing wrong here, I looked at the error, but I can't seem to figure it out.
    SQL> CREATE OR REPLACE TRIGGER TRANS_AskingPriceIniValue
    2 AFTER INSERT ON Transactions
    3 DECLARE
    4 rowcount INTEGER;
    5 NetProfit NUMBER;
    6 avgNetProfit NUMBER;
    7 BEGIN
    8 SELECT Count(*) INTO rowcount
    9 FROM Transactions T
    10 WHERE new.WorkID = T.WorkID;
    11 IF rowcount := 1 THEN
    12 new.AskingPrice = 2 * new.AquisitionPrice;
    You have the assignment operator ( := ) and the equality operator ( = ) mixed up.
    The 2 lines above should probably be
    IF rowcount = 1 THEN				-- = for equality
        new.AskingPrice := 2 * new.AquisitionPrice;	-- := for assignment
    13 ELSE IF
    14 rowcount > 1 THEN SELECT Sum(NetPRofit) INTO NetProfit
    15 FROM ArtistWorkNetView AW
    16 WHERE AW.WorkID = new.WorkID
    This is correct: you're using = to compare 2 values.
    17 GROUP BY AW.WorkID;
    18 avgNetProfit = NetProfit / (rowcount - 1);
    This is wrong. Use := to assign a value to a variable:
    avgNetProfit := NetProfit / (rowcount - 1);
    19 IF avgNetProfit > 2 * new.AcquisitionPrice THEN
    20 new.AskingPrice = avgNetProfit;
    Use := here, too.
    21 ELSE
    22 new.AskingPrice = 2 * new.AcquistionPrice;
    Once again.
    23 END IF;
    24 END IF;
    25 END IF;
    26 END;
    27 /

    Warning: Trigger created with compilation errors.

    SQL> SHOW ERRORS
    Errors for TRIGGER TRANS_ASKINGPRICEINIVALUE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    9/13 PLS-00103: Encountered the symbol "=" when expecting one of the
    following:
    . ( * @ % & = - + < / > at in is mod not rem then
    <an exponent (**)> <> or != or ~= >= <= <> and or like
    between ||
    When compiling a trigger causes an error, the line numbers in the error message are relative to the first DECLARE or BEGIN statement. In your case, there were 2 lines of code before the DECLARE, so line 9 in the error message actually corresponds to line 9 + 2 = 11 in your source code.

    Edited by: Frank Kulash on Apr 9, 2013 3:07 PM
    Frank Kulash
  • 1002068
    1002068 Member Posts: 12
    I Made those changes + A couple others and now I am getting this error

    SQL> CREATE OR REPLACE TRIGGER TRANS_AskingPriceIniValue
    2 AFTER INSERT ON Transactions
    3 DECLARE
    4 rowcount INTEGER;
    5 NetProfit NUMBER;
    6 avgNetProfit NUMBER;
    7 BEGIN
    8 SELECT Count(*) INTO rowcount
    9 FROM Transactions T
    10 WHERE new.WorkID = T.WorkID;
    11 IF rowcount = 1 THEN
    12 new.AskingPrice := 2 * new.AquisitionPrice;
    13 ELSE IF
    14 rowcount > 1 THEN SELECT Sum(NetPRofit) INTO NetProfit
    15 FROM ArtistWorkNetView AW
    16 WHERE AW.WorkID = new.WorkID
    17 GROUP BY AW.WorkID;
    18 avgNetProfit := NetProfit / (rowcount - 1);
    19 IF avgNetProfit > 2 * new.AcquisitionPrice THEN
    20 new.AskingPrice = avgNetProfit;
    21 ELSE
    22 new.AskingPrice = 2 * new.AcquistionPrice;
    23 END IF;
    24 END IF;
    25 END IF;
    26 END;
    27 /

    Warning: Trigger created with compilation errors.

    SQL> SHOW ERRORS
    Errors for TRIGGER TRANS_ASKINGPRICEINIVALUE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    18/18 PLS-00103: Encountered the symbol "=" when expecting one of the
    following:
    := . ( @ % ;
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,921 Red Diamond
    edited Apr 9, 2013 3:22PM
    Hi,
    999065 wrote:
    I Made those changes + A couple others and now I am getting this error
    ...
    20 new.AskingPrice = avgNetProfit;
    You missed this one; it should be
    new.AskingPrice := avgNetProfit;	-- := for assignment
  • 1002068
    1002068 Member Posts: 12
    SQL> SHOW ERRORS
    Errors for TRIGGER TRANS_ASKINGPRICEINIVALUE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/2 PL/SQL: SQL Statement ignored
    8/12 PL/SQL: ORA-00904: invalid column name
    10/1 PLS-00201: identifier 'NEW.ASKINGPRICE' must be declared
    10/1 PL/SQL: Statement ignored
    12/19 PL/SQL: SQL Statement ignored
    14/23 PL/SQL: ORA-00904: invalid column name
    17/1 PL/SQL: Statement ignored
    17/23 PLS-00201: identifier 'NEW.ACQUISITIONPRICE' must be declared
    SQL>

    And now I'm getting everything else that was messed up
  • _LC_
    _LC_ Member Posts: 94
    : before new

    meaning :new.columnName
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,921 Red Diamond
    edited Apr 9, 2013 3:30PM
    Hi,
    999065 wrote:
    ... And now I'm getting everything else that was messed up
    Whenever you have a problem, please post a complete test script that people can run to re-create the problem and test their ideas.
    In this case, post CREATE TABLE and INSERT statements for all the tables involved, showing the tables as the exist before the trigger fires. Also, post a DML statement or two that will cause the trigger to fire, and the results you want (that is, the contents of the changed table, transactions) after each one.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 1002068
    1002068 Member Posts: 12
    Do I do that to ONLy the first instance or do I place : before all cases of new.value
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,921 Red Diamond
    edited Apr 9, 2013 3:36PM
    Hi,
    999065 wrote:
    Do I do that to ONLy the first instance or do I place : before all cases of new.value
    You need the : before all the NEWs.

    You'll find it just as fast and convenient to see for yourself as it is to ask on this forum. Change just the first NEW, and compile again. If the first group of error messages disappears, but the second one remains, you'll know you need to do the same thing for the second one.

    Edited by: Frank Kulash on Apr 9, 2013 3:33 PM
  • 1002068
    1002068 Member Posts: 12
    USING ORACLE SQL*PLUS 9.0.1.0.0

    /* Creating the Transactions table */
    CREATE TABLE Transactions (TransactionID INTEGER PRIMARY KEY, DateAcquired CHAR(10), AcquisitionPrice NUMBER(10,2), AskingPrice NUMBER(10,2), DateSoldID CHAR(10), SalesPrice NUMBER(10,2), CustomerID INTEGER, WorkID INTEGER, FOREIGN KEY (CustomerID) REFERENCES Customers, FOREIGN KEY (WorkID) REFERENCES Works);
    /* Creating the Works table */
    CREATE TABLE Works (WorkID INTEGER PRIMARY KEY, Title CHAR(30), Medium CHAR(30), Description CHAR(50), Copy CHAR(10),ArtistID INTEGER, FOREIGN KEY (ArtistID) REFERENCES Artists);

    /* Creating the Customers table */
    CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    LastName CHAR(15),
    FirstName CHAR(15),
    Street CHAR(30),
    City CHAR(20),
    State CHAR(2),
    ZipPostalCode CHAR(10),
    Country CHAR(20),
    AreaCode CHAR(3),
    PhoneNumber CHAR(8),
    Email CHAR(40)
    );
    /

    /* Creating ArtistWorksNetView */

    CREATE VIEW ArtistWorkNetView AS SELECT W.WorkID, LastName AS ArtistLastName, FirstName AS
    ArtistFirstName, Title, Copy, DateSoldID, AcquisitionPrice, SalesPrice, (SalesPrice - AcquisitionPrice)
    AS NetProfit FROM Transactions T JOIN Works W ON T.WorkID = W.WorkID JOIN Artists A ON
    W.ArtistID = A.ArtistID;

    /* Create Trigger TRANS_AskingPriceIniValue */

    CREATE OR REPLACE TRIGGER TRANS_AskingPriceIniValue
    AFTER INSERT ON Transactions
    DECLARE
    rowcount INTEGER;
    NetProfit NUMBER(10,2);
    avgNetProfit NUMBER;
    /*new.AskingPrice NUMBER(10,2);
    new.AcquisitionPrice NUMBER(10,2);*/
    BEGIN
    SELECT Count(*) INTO rowcount
    FROM Transactions T
    WHERE :new.WorkID = T.WorkID;

    IF rowcount = 1 THEN
    :new.AskingPrice := 2 * :new.AquisitionPrice;
    ELSE IF

    rowcount > 1 THEN SELECT Sum(NetPRofit) INTO NetProfit
    FROM ArtistWorkNetView AW
    WHERE AW.WorkID = new.WorkID
    GROUP BY AW.WorkID;

    avgNetProfit := NetProfit / (rowcount - 1);
    IF avgNetProfit > 2 * new.AcquisitionPrice THEN
    new.AskingPrice := avgNetProfit;
    ELSE

    new.AskingPrice := 2 * new.AcquistionPrice;
    END IF;
    END IF;
    END IF;
    END;


    *** Sorry I know I should have done that in the first place did not know this question was going to lead me to error after error, thought it could be fixed with a couple small fixes
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,921 Red Diamond
    edited Apr 9, 2013 3:47PM
    Hi,
    999065 wrote:
    ...
    *** Sorry I know I should have done that in the first place did not know this question was going to lead me to error after error, thought it could be fixed with a couple small fixes
    You can expect that 27 lines of code will produce error after error; often the same error repeated over and over. Don't write so much code at once. Take baby steps. Write the smallest possible amount of code, test, correct if necessary and test again.
    For example, the first version of this trigger might be:
    CREATE OR REPLACE TRIGGER TRANS_AskingPriceIniValue
    AFTER INSERT ON Transactions
    BEGIN
       :NEW.askingprice := -123;
    END;
    This is enough to see that the trigger is firing, and that it can derive a value for the column. Is this trigger doing just what you want? Of course not; it's jsut one step; hopefully a step in the right direction, but if it's in the wrong direction, it's just one step to have to undo, not 4 steps, or 27.

    When that much is working, add another line or two and test again. That won't keep you from confgusing = with :=, for example, but it will let you know about the mistake before you repeat it 4 times.
This discussion has been closed.