6 replies to this topic

[fread]

I have this trigger code:

Create or Replace 
trigger UpdateStatus
Before insert or update 
of unitsinstock, ReorderLevel 
on Product
For each row
Begin
if :new.unitsinstock <= :new.reorderlevel 
then
    :new.reorderstatus :='1';
else
    :new.reorderstatus :='0';
end if;
end;

which processes successfully. I am attempting to execute this update

update product
set unitsinstock = 5
where upper(productId) = '3043M';

but i keep getting this error message:

Quote

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

I had a look at a couple articles and this ORA-04098: trigger "string.string" is invalid and failed re-validation tips one in particular suggest duplicate triggers as one possible reason. But I doubt that is the case since after I ran the trigger the output was: trigger processed. [TABLE]
[TR]
[TD][/TD]
[/TR]
[/TABLE]

---------- Post added at 04:20 PM ---------- Previous post was at 03:05 PM ----------

Edit: I think it is the trigger. The database has it listed as INVALID. :confused:

[WingedPanther]

What is your database structure and schema?

[fread]

I will describe the product table only since the only foreign item is the vendor table and the trigger does not mess with that value. Also I am using Oracle Database 10g Express Edition on Windows 7.

create table Product(
    ProductId char(5) not null,
    ProductName varchar2(30) not null,
    VendorID varchar2(5) not null,
    UnitPrice number(8,2) not null,
    UnitsInStock number(3) not null,
    ReorderQuantity number(3),
    ReorderFlag char(1) default 'N',
    primary key(ProductID),
    foreign key(VendorID) references Vendor(VendorID)
    );



    alter table product
    add(ReorderLevel Number(3) default 10);

I am not sure what else will be relevant.

[WingedPanther]

Your trigger uses reorderlevel, but your table uses ReorderQuantity. I'm guessing the field name mis-match is your issue.

[fread]

This:

alter table product     add(ReorderLevel Number(3) default 10);

Is run before I run the trigger. I viewed the table to very the attribute was added successfully. I am doubting its a mismatch. Also case does not matter with this database. So Product is the same as product

[WingedPanther]

There's no reorderstatus field

[fread]

It couldn't be more obvious; Silly me. Reason: I was using the online database from school. I completely forgot I changed that attribute name on my personal machine, I did not even bother to check since the trigger works fine on the schools database.
Much Thanks.

Oh my, I just remembered I changed a lot of attribute names and data types on my machine. :scared: Catastrophe: views,procedures and more triggers :scared: