delete data using a trigger

[wizardofsilence]

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...

 

[Dagon]

Try changing

IF :OLD.EVDATE >= 'SYSDATE' THEN

to

IF :OLD.EVDATE >= SYSDATE THEN

 

[wizardofsilence]

@ Dagon

I corrected it and tryed.

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;

using above I get

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


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



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

GKD
thank you.

 

[carp]

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

 

[wizardofsilence]

@carp

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

thank you
GKD

 

[wizardofsilence]

@carp

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

GKD