Problem creating a trigger.

[EM1107]

Good day everyone!

I am new to the world of Oracle and my boss as asked me to create a trigger. The purpose of the triger is to insert default values in the field when the row is been inserted.

If someone change the value of the PD_OBJ_TYPE to a 1 than I want to have the values of the other fields change also.

If someone change the value of PD_OBJ_TYPE to 0 again, I want the values change back as if they where first inserted.


Here is my trigger. Can anyone tell me why it does not work and what I should to make it work?

Thanks!

CREATE OR REPLACE TRIGGER "TRG_RO_TEST"
BEFORE INSERT OR UPDATE OF "APPLICATION", "CLASSIFIED", "CREATION_DATE", "LAST_ACCESS_TIME",
"LAST_EDIT_DATE", "OBJ_DATE", "PD_OBJ_TYPE", "READONLY_DATE", "RIMSOFFICIAL", "STATUS"
ON "PROFILE"
FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.READONLY_DATE := ld.CREATION_DATE;
:NEW.STATUS := 19;
:NEW.PD_OBJ_TYPE := 0;
:NEW.OBJ_DATE := SYSDATE;
:NEW.RIMSOFFICIAL := 1;
:NEW.CLASSIFIED := 1;
ELSE
BEGIN
IF :NEW.PT_OBJ_TYPE = 1
THEN
:NEW.STATUS := 0;
:NEW.RIMSOFFICIAL := 0;
:NEW.CLASSIFIED := 0;
ELSE
:NEW.READONLY_DATE := ld.CREATION_DATE;
:NEW.STATUS := 19;
:NEW.PD_OBJ_TYPE := 0;
:NEW.OBJ_DATE := SYSDATE;
:NEW.RIMSOFFICIAL := 1;
:NEW.CLASSIFIED := 1;
END IF;
END IF;
END;
END;

 

[SantaMufasa]

Can anyone tell me why it does not work and what I should to make it work?

Does "it does not work" mean that you are receiving:


1) A syntax error. Oracle does not create the trigger because you have violated an Oracle trigger-definition rule.

2) A run-time error. Oracle creates the trigger, but when the trigger executes, Oracle throws an error that indicates that something has occurred that prevent Oracle from completing the instructions that your trigger contains.

3) A logic error. Oracle creates the trigger; the trigger runs to completion; but the results are not what you want.

Please post the error message (for error-types #1 or #2) or (in case of #3) what you want to occur that is not occurring.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[EM1107]

1) When I create the trigger I receive the following error.

ORA-24344: Success with compilation error.

2) If I try to input information in the database I receive the following error:

ORA-04098: trigger 'trigger_name' is invalid and failed re-validation.

3) What I expect the trigger to do is to modify the values as shown in the trigger.

 

[SantaMufasa]

EM,

Here are some issues that, I suspect, may be contributing to your problems:

1) Is it true that table, PROFILE, contains these two columns: "PD_OBJ_TYPE" and "PT_OBJ_TYPE"? If so, fine; if not, then let's fix your trigger code to match the table reality.

2) You "IF...THEN...ELSE...END IF" and "BEGIN...END" structures are incorrectly overlapping: Your inner "BEGIN" block contains and "END IF" that matches to an "IF" that is outside the inner "BEGIN" block.

Once you resolve these issues, please re-run and let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

Also, if you are inserting a new row, what value do you think you will find in ld.CREATION_DATE?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[EM1107]

This is perfect. I with the information you provided I was able to resolve the issue.

Thanks!