Oracle Warning: Trigger Created With Compilation Errors

1002068

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 ||

Frank Kulash

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

1002068

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

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

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_

: before new

meaning :new.columnName

Frank Kulash

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

Do I do that to ONLy the first instance or do I place : before all cases of new.value

Frank Kulash

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

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

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.