SQL Insert Statement causing error in Oracle

Question

I'm trying to do this

INSERT INTO INSPECTIONOFFICE (INSPOFFICE_ID, INSPOFFICE_NAME, INSPOFFICE_STREET, INSPOFFICE_CITY, INSPOFFICE_STATE, INSPOFFICE_ZIPCODE, INSPOFFICE_CONTPERSONFNAME, INSPOFFICE_CONTPERSONLNAME, INSPOFFICE_CONTPERSONPHONE, INSPOFFICE_CONTPERSONEMAIL)
VALUES (12, 'InspectionOffice1', '121 Ames st', 'Omaha', 'NE', 68128, 'Brad', 'Foley', '4022853487', '[email protected]');

but I get an error

Error starting at line : 22 in command -
INSERT INTO INSPECTIONOFFICE (INSPOFFICE_ID, INSPOFFICE_NAME, INSPOFFICE_STREET, INSPOFFICE_CITY, INSPOFFICE_STATE, INSPOFFICE_ZIPCODE, INSPOFFICE_CONTPERSONFNAME, INSPOFFICE_CONTPERSONLNAME, INSPOFFICE_CONTPERSONPHONE, INSPOFFICE_CONTPERSONEMAIL)
VALUES (12, 'InspectionOffice1', '121 Ames st', 'Omaha', 'NE', 68128, 'Brad', 'Foley', '4022853487', '[email protected]')
Error at Command Line : 22 Column : 13
Error report -
SQL Error: ORA-04098: trigger 'JMEEK.INSPECTIONOFFICE_INSPECTIONOFF' is invalid and failed re-validation
04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.

Any idea how to fix this?

Looks like There is a trigger written on the table for insert event. Trigger is executed whenever the event happens, in this case it's insert event. tutorialspoint.com/plsql/plsql_triggers.htm when you execute this insert query, the trigger is executed and logic in the trigger is failing the validation. That's why you're seeing this error. — Chetan
– Chetan, Commented Dec 12, 2019 at 2:14

Frederic · Accepted Answer · 2019-12-12 02:27:15Z

seems you do not have permission access to the trigger JMEEK.INSPECTIONOFFICE_INSPECTIONOFF

or have some compilation problem with it...

you should take a look in the trigger.

or if you don't know what a trigger is, call a experienced job's teammate. :)

btw, if you are alone, and it don't matter too much.. you can disable / drop the trigger..

this command disable, you can enable later

ALTER TRIGGER JMEEK.INSPECTIONOFFICE_INSPECTIONOFF DISABLE;

this command drop the trigger, you will lose it forever (if you don't have a backup)

drop trigger JMEEK.INSPECTIONOFFICE_INSPECTIONOFF;

Bob Jarvis - Слава Україні · Accepted Answer · 2019-12-12 03:07:36Z

The trigger JMEEK.INSPECTIONOFFICE_INSPECTIONOFF, which I am guessing you wrote because it's in the JMEEK schema, which (again I'm guessing) seems to be yours, is invalid, meaning it has compilation errors. To find out what those errors are you should execute the query

SELECT *
  FROM ALL_ERRORS
  WHERE OWNER = 'JMEEK' AND
        NAME = 'INSPECTIONOFFICE_INSPECTIONOFF'
  ORDER BY SEQUENCE

None of us can do much else for you because we don't know what this trigger looks like. If you need to see the source code you can execute the following query:

SELECT LINE, TEXT
  FROM ALL_SOURCE
  WHERE OWNER = 'JMEEK' AND
        NAME = 'INSPECTIONOFFICE_INSPECTIONOFF'
  ORDER BY LINE

Or you can use an IDE such as Oracle's free SQL Developer product which will make all this a lot easier for you.

Collectives™ on Stack Overflow

SQL Insert Statement causing error in Oracle

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related