Converting mysql trigger to pl/sql trigger

Question

I have created a trigger for my mysql project previously and it was working well. However, I am trying to change this trigger to make suitable for pl-sql(oracle). This is my original code which works in the mysql:

DELIMITER $$
CREATE TRIGGER course_title_delete AFTER DELETE on Course
FOR EACH ROW
BEGIN
DECLARE rownumber INT;
SET rownumber = (SELECT COUNT(*) FROM Course 
    WHERE Course_code=old.Course_code);
IF rownumber = 0
THEN
DELETE FROM Course_title
    WHERE Course_title.Course_code=old.Course_code;
    END IF;
END$$
DELIMITER ;

And this one is the same code which I have tried to convert pl-sql format. However it is not working, when I upload as a script and try to run it in apex.

CREATE OR REPLACE TRIGGER course_title_delete 
AFTER DELETE ON course
FOR EACH ROW
BEGIN
DECLARE rownumber INT;
SET rownumber = (SELECT COUNT(*) FROM course 
    WHERE course_code= :old.course_code);
IF rownumber = 0
THEN
DELETE FROM course_title
    WHERE course_title.course_code:=:old.course_code;
    END IF;
END;
/

What is the error? What do you mean by "not working"? In both databases, it looks like you can replace the trigger with cascading foreign key constraints. — Gordon Linoff
– Gordon Linoff, Commented Jan 9, 2016 at 12:42
"Error at line 3: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; not null range default character " this is the error, when I runned it in the apex sql scripts part — Ali Can Üstünel
– Ali Can Üstünel, Commented Jan 9, 2016 at 12:47

Gordon Linoff · Accepted Answer · 2016-01-09 13:09:19Z

1

At the very least, you have a syntax error in this line:

DELETE FROM course_title
    WHERE course_title.course_code:=:old.course_code;
----------------------------------^

That should just be =.

Also, in Oracle, the DECLARE goes before the BEGIN, not after.

Also, in Oracle, this line:

SET rownumber = (SELECT COUNT(*) FROM course 
   WHERE course_code= :old.course_code);

should be:

SELECT COUNT(*) INTO rownumber 
FROM course
WHERE course_code = :old.course_code;

Well, actually, the is the correct syntax for what you are expressing. But, you should be using NOT EXISTS rather than COUNT(*) in both databases.

In both databases, I think you can replace this trigger with a cascading delete foreign key constraint. Also, you can simplify the logic to eliminate the if:

CREATE OR REPLACE TRIGGER course_title_delete 
AFTER DELETE ON course
FOR EACH ROW
BEGIN
    DELETE FROM course_title
    WHERE NOT ExISTS (SELECT 1
                      FROM course c
                      WHERE c.course_code = :old.course_code
                     ) AND
          course_code = :old.course_code;
END;

edited Jan 9, 2016 at 13:09

answered Jan 9, 2016 at 12:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ali Can Üstünel Over a year ago

Sir I am new at databases, can you explain that what is cascading delete foreign key constraint. You mean there is a problem on my foreign keys? When I make ':=' to '=', then it gives this error "Error at line 3: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; not null range default character"

Gordon Linoff Over a year ago

@AliCanÜstünel . . . You cannot be that new to databases if you are using triggers. Here is one explanation: techonthenet.com/oracle/foreign_keys/foreign_delete.php. Also, the MySQL documentation is usually pretty good at explaining such concepts: dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html.

Bob Jarvis - Слава Україні Over a year ago

@GordonLinoff - I'm curious - this is a row trigger which is accessing the table (ORDER) on which the trigger is defined, so why doesn't this trigger raise a "MUTATING TABLE" exception in Oracle? Thanks.

Gordon Linoff Over a year ago

@BobJarvis . . . I'm not 100% sure that it doesn't. But if it doesn't then that would be because it is an after delete trigger. Cascading deletes really are the way to go. This answer was focusing on the syntax issues.

AndrewMcCoist Over a year ago

NOT EXISTS might cause efficiency problems in some cases. Isn't it better to turn it into EXISTS (or select with ROWNUM = 1 to use stop key) and use the inverted condition? (or just SELECT NVL((SELECT 1 FROM course WHERE course_code = :old.course_code AND ROWNUM=1),0) FROM dual)

|

Collectives™ on Stack Overflow

Converting mysql trigger to pl/sql trigger

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related