Trigger Prob.

Hello All

I have used the trigger suggested as follows (removing new):

CREATE TRIGGER oTrigger AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF (paymentStatus IS NOT NULL AND deliveryStatus IS NOT NULL)
INSERT INTO storedAt VALUES (PID, WID, Number)
END IF
END

The problem is that I get the following error after updating the table:
" User.Otrigger is invalid and failed re-validation". No change is made to the database.

I wish the variables paymentStatus, deliverystatus, PID, WID, and Number to be from the updated tuple only.

Any help is appreciated.

Thanks

Chris

hi there ,

instead of using after update trigger why don't you use before update trigger...

the trigger will work this way, it will check the two columns .if they satisfy your condition then only it will perform the update part.

You should have read my example more closely

Instead of

IF (paymentStatus IS NOT NULL AND deliveryStatus IS NOT NULL)

you have to write:

IF (new.paymentStatus IS NOT NULL AND new.deliveryStatus IS NOT NULL)

Thanks for all the help. I abandoned that trigger as the tasks I needed to complete were to complicated to put in a trigger.

I am trying to create a simpler trigger though.... This would have to be about as easy as I could do.

When I add a tuple to product through:

INSERT INTO PRODUCT VALUES (pid, wid, name);

I want a tuple to automatically be added to STOREDAT like:

INSERT INTO STOREDAT VALUES (pid, wid, 0);

This is my trigger, which causes validation error:

CREATE TRIGGER pInsert_T AFTER INSERT ON product
FOR EACH ROW
BEGIN
INSERT INTO storedAt VALUES (new.PID, new.WID, 0)
END;

What is wrong here?

Thanks

Chris

use colon (:) with new, like this

INSERT INTO storedAt VALUES (:new.PID, :new.WID, 0);

Cheers for that,

I'm understanding the concept of new, old, and how the triggers work now.

String orderT = "CREATE TRIGGER orderT BEFORE UPDATE ON orders " +
"FOR EACH ROW " +
"BEGIN " +
"IF (deliveryStatus = 'paid' AND paymentStatus = 'paid') THEN " +
"RAISE_APPLICATION_ERROR(-20000, 'A completed order cannot be updated') " +
"END IF " +
"END";

This is my trigger in the form of a Java string. Do i have to embed any semi colons in it?

Here is my trigger in a more easy to read form. Of course, it does not work. Triggers are the most annoying thing I have come across!

CREATE TRIGGER orderT BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
IF (deliveryStatus = 'delivered' AND paymentStatus = 'paid') THEN
RAISE_APPLICATION_ERROR(-20000, 'A completed order cannot be updated') <--- WOULD I HAVE TO PUT A ; HERE?
END IF
END

Also, does anyone know why I get a validation error with this one?

The purpose of this trigger it to prevent modifications being made to an order tuple if deliveryStatus = delivered and paymentStatus = paid in that tuple.

Thanks

Chris