INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

delete data using a trigger

delete data using a trigger

(OP)
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...

RE: delete data using a trigger

Try changing

IF :OLD.EVDATE >= 'SYSDATE' THEN

to

IF :OLD.EVDATE >= SYSDATE THEN

RE: delete data using a trigger

(OP)
@ Dagon

I corrected it and tryed.

CODE -->

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);)

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.

RE: delete data using a trigger

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

RE: delete data using a trigger

(OP)
@carp

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

thank you
GKD

RE: delete data using a trigger

(OP)
@carp

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

thumbsup2 GKD

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close