Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Oracle Warning: Trigger Created With Compilation Errors

1002068
Member Posts: 12
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 ||
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 ||
Tagged:
Answers
-
Hi,
Welcome to the forum!999065 wrote:You have the assignment operator ( := ) and the equality operator ( = ) mixed up.
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;
The 2 lines above should probably beIF rowcount = 1 THEN -- = for equality new.AskingPrice := 2 * new.AquisitionPrice; -- := for assignment
13 ELSE IFThis is correct: you're using = to compare 2 values.
14 rowcount > 1 THEN SELECT Sum(NetPRofit) INTO NetProfit
15 FROM ArtistWorkNetView AW
16 WHERE AW.WorkID = new.WorkID17 GROUP BY AW.WorkID;This is wrong. Use := to assign a value to a variable:
18 avgNetProfit = NetProfit / (rowcount - 1);avgNetProfit := NetProfit / (rowcount - 1);
19 IF avgNetProfit > 2 * new.AcquisitionPrice THENUse := here, too.
20 new.AskingPrice = avgNetProfit;21 ELSEOnce again.
22 new.AskingPrice = 2 * new.AcquistionPrice;23 END IF;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.
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 ||
Edited by: Frank Kulash on Apr 9, 2013 3:07 PM -
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:
:= . ( @ % ; -
Hi,999065 wrote:You missed this one; it should be
I Made those changes + A couple others and now I am getting this error
...
20 new.AskingPrice = avgNetProfit;new.AskingPrice := avgNetProfit; -- := for assignment
-
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 -
: before new
meaning :new.columnName -
Hi,999065 wrote:Whenever you have a problem, please post a complete test script that people can run to re-create the problem and test their ideas.
... And now I'm getting everything else that was messed up
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} -
Do I do that to ONLy the first instance or do I place : before all cases of new.value
-
Hi,999065 wrote:You need the : before all the NEWs.
Do I do that to ONLy the first instance or do I place : before all cases of new.value
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 -
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 -
Hi,999065 wrote: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.
...
*** 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
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.