Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: writting select query inside a trigger

    I am trying to write a trigger which can fill a table say A before inserting data in B.

    There are atleast 3 tables which i have to deal with before i can insert data in A.

    First i have to get an id from B then compare it with table C and get a field from there once i get the field i have to get id from A based on the values from C.

    I have written a trigger which shows errors. I have gone through some sites but failed in gaining relevent information.

    Can anyont suggest if its good to write such a trigger or is it better to do it programmatically in php.

    Heres my trigger..

    DROP TRIGGER IF EXISTS `forum_course_create`;
    DELIMITER //
    CREATE TRIGGER `forum_course_create` BEFORE INSERT ON `course`
    FOR EACH ROW BEGIN
    DECLARE x VARCHAR(50);
    SET x = (select cat_name from category where cat_id = NEW.cat_id);
    DECLARE y INTEGER
    SET y = (select id from forum_category where category_name = x);
    insert into forum_category (category_name,category_desc,parent_category) values (NEW.course_name,NEW.course_description,y);
    END IF
    END
    //
    DELIMITER ;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Keep all the DECLARE statements as the first statements in your code:

    Also what happens if the SELECT statements do not return any value. You should include some kind of handler in there

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET ....


    Code:
    DROP TRIGGER IF EXISTS `forum_course_create`;
    DELIMITER //
    CREATE TRIGGER `forum_course_create` BEFORE INSERT ON `course`
    FOR EACH ROW BEGIN
    DECLARE x VARCHAR(50);
    DECLARE y INTEGER
    SET x = (select cat_name from category where cat_id = NEW.cat_id);
    SET y = (select id from forum_category where category_name = x);
    insert into forum_category (category_name,category_desc,parent_category) values (NEW.course_name,NEW.course_description,y);
    END IF
    END
    //
    DELIMITER ;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •