Results 1 to 3 of 3

Thread: MySQL trigger

  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: MySQL trigger

    Hi,

    I would like to implement a trigger on my tables. My structure is simple. You have a leaf and Branch:

    -Table Leafs
    id (PK)
    branchid (FK)

    ============

    -Table Branch
    id (PK)
    leafcount (needs to be triggered)


    Simple, a branch has many leafs. But when I insert a new leaf, I need the Branch(leafcount) to increment.

    Any ideas?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You will need 3 triggers, one during insert to increment the leafcount, one during insert to decrement the leaf count and one during the update to verify that the branch is not changing from one to another in which case you will need to decrement one and increment another leafcount. To verify the update use the old.branchid and new.branchid while determining what to do. Of course if they are the same then no new calculation is required.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jun 2013
    Posts
    1
    Hi Guys,

    I have a problem with mysql trigger. My trigger doesn't fire if insert request comes from a host other than the localhost.

    my trigger is

    DELIMITER $$

    CREATE DEFINER = 'root'@'%' TRIGGER add_user BEFORE INSERT ON Users
    FOR EACH ROW BEGIN

    SET @exec_var = sys_exec(CONCAT('/opt/user_add.pl ', NEW.username));
    END;
    $$

    DELIMITER ;


    SHOW TRIGGERS\G;

    *************************** 1. row ***************************
    Trigger: add_user
    Event: INSERT
    Table: Users
    Statement: BEGIN

    SET @exec_var = sys_exec(CONCAT('/opt/user_add.pl ', NEW.username));
    END
    Timing: BEFORE
    Created: NULL
    sql_mode:
    Definer: root@%

    Any ideas?
    Last edited by lakmal84; 06-29-13 at 11:50.

Posting Permissions

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