Q- ensure that event records can not be deleted if the "evdate" is not later than the current date

I created the following trigger for this matter.

CREATE OR REPLACE TRIGGER TRIGTEST3
BEFORE DELETE OR UPDATE OF evdate ON event
FOR EACH ROW
BEGIN
IF :OLD.EVDATE >= 'SYSDATE' THEN
RAISE_APPLICATION_ERROR(-20101,'OUT OF RANGE');
--ELSE
-- DELETE FROM cusarrive c , event e where e.eventID = c.eventID AND evdate = evdate;
--delete from event where evdate = evdate;

END IF;
END;

delete from event where eventID = 004;

trigger executes but when deleting..

it gives:

ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "TEST.TRIGTEST3", line 2
ORA-04088: error during execution of trigger 'TEST.TRIGTEST3'

1. delete from event where eventID = 004;
.....

I want to delete data using the given scenario and the code should delete the related (cusarrive) table's data also..


thanks for the help in advance.

GKD...

Try changing

IF :OLD.EVDATE >= 'SYSDATE' THEN

to

IF :OLD.EVDATE >= SYSDATE THEN

@ Dagon

I corrected it and tryed.


using above I get

delete from event where eventID = 004;
(which is -- INSERT INTO Event VALUES(004,'20-OCT-2012','20:30',22,9010);)

Quote (ORA-20101: OUT OF RANGE
ORA-06512: at "TEST.TRIGTEST3", line 3
ORA-04088: error during execution of trigger 'TEST.TRIGTEST3'
1. delete from event where eventID = 004;)

But

when deleting..
delete from event where eventID = 111;
(which is INSERT INTO Event VALUES(111,'11-JAN-2009','16:00',31,9010);)

i get

Quote (ORA-02292: integrity constraint (TEST.FK_EVENTID) violated - child record found)

How to delete the child record using this same trigger when the date matches to the scenario ?

GKD
thank you.

One way would be to modify your foreign key constraint to add "ON DELETE CASCADE'.

@carp

Is it to make when I create the tables..Or could please show me show to do that

thank you
GKD

@carp

Thanks for the help I just Altered the tables and tried.. it s working ... thanks alt

GKD