orc-04098 Error when using a Trigger in Oracle.

Question

0.00/5 (No votes)

See more:

Hey all, I'm developing an Oracle database -I'm kinda new to oracle-, and I want to use triggers. I'm using oracle 11g, I have a table called Computer. When I run the below trigger, it debugs and gives no errors, BUT! once I try to insert any data into Computer, it fires an error and says I have to either drop, or disable the trigger, or I have privileges/authorization issues!

SQL

CREATE OR REPLACE TRIGGER "MyUser"."TRG2TEST"
BEFORE INSERT ON COMPUTER
DECLARE NEW AS NEWROW
BEGIN
INSERT INTO COMPUTER (COMPID,COMPNAME,CPU,hdd_size,motherboard,operatingsystem,visible,good)  VALUES ((select max(CompID) from IDZ)+1,newRow.CompName,newRow.CPU,newRow.HDD_Size,newRow.MotherBoard,newRow.OperatingSystem,1,1);
END;

Please note that I tried this code using Oracle SQL Developer.
when I go to SQL-Plus to run this code (As system), it always stops at the name of the table: "Computer"
I really need your help. Thank you in advance guys!

Posted 29-Dec-11 23:27pm

mafnx

Updated 29-Dec-11 23:31pm

Wendelius

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-12-29T23:36:00

Solution 1

Typically this means that you have an error in your trigger. Using for example SQL*Plus run command

SHOW ERRORS TRIGGER TriggerName

To see what are the errors in comiplation.

Addition:

SQL

CREATE OR REPLACE TRIGGER "MyUser"."TRG2TEST"
BEFORE INSERT ON COMPUTER
FOR EACH ROW
BEGIN
  INSERT INTO COMPUTER (COMPID,COMPNAME,CPU,hdd_size,motherboard,operatingsystem,visible,good)  
  VALUES   
  (SEQ_CompID.NextVal, :new.CompName, :new.CPU, :new.HDD_Size, :new.MotherBoard, :new.OperatingSystem,1,1);
END;

Addition 2:
Added FOR EACH ROW because this should fire separately on each new row that is inserted.

Posted 29-Dec-11 23:36pm

Wendelius

Updated 30-Dec-11 7:17am

v3

Comments

mafnx 30-Dec-11 5:54am

is there any thing wrong in my trigger? I mean code wise?
plus, is there a way to make the creation ID of a table auto-generated? when I used to work with SQL Server 2005, I used to set the ID column to auto-defined, select a seed and set an increment. life was easy! but I'm struggling with Oracle. Thank you in advance.

Wendelius 30-Dec-11 6:11am

One thing is that don't select max from a table to acquire a new key value. Two different sessions may get the same value. Use sequences instead.

Also I think you have syntactical problems in the insert statement. See the updated answer.

mafnx 30-Dec-11 5:56am

btw I ran sql plus and checked for errors and the resulting message was "no errors"
any hints?

Wendelius 30-Dec-11 13:18pm

Regarding to your latest question, answer updated.