#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535

    Using stored proceedures with triggers


    I am using similar triggers on a bunch of tables as shown under the first block of code. I am thinking of using a stored procedure so I don't duplicate the code as shown under the second block of code. Am I doing it correctly (yes, I tested it and it appears to work ). Thanks

    Code:
    DELIMITER $$
    
    CREATE TRIGGER tg_TypicalTable_ins BEFORE INSERT ON TypicalTable FOR EACH ROW
    BEGIN
        IF NEW.created_by_user IS NULL OR NEW.created_by_user = '' THEN
            SET NEW.created_by_user = @users_id;
        END IF;
        IF NEW.modified_by_user IS NULL OR NEW.modified_by_user = '' THEN
            SET NEW.modified_by_user = @users_id;
        END IF;
    END$$
    
    CREATE TRIGGER tg_TypicalTable_upd BEFORE UPDATE ON TypicalTable FOR EACH ROW
    BEGIN
        IF NEW.modified_by_user = OLD.modified_by_user THEN
            SET NEW.modified_by_user = @users_id;
        END IF;
    END$$
    
    DELIMITER ;
    Code:
    DELIMITER $$
    
    CREATE PROCEDURE `createRecord` ()
    BEGIN
        IF NEW.created_by_user IS NULL OR NEW.created_by_user = '' THEN
            SET NEW.created_by_user = @users_id;
        END IF;
        IF NEW.modified_by_user IS NULL OR NEW.modified_by_user = '' THEN
            SET NEW.modified_by_user = @users_id;
        END IF;
    END$$
    
    CREATE PROCEDURE `modifyRecord` ()
    BEGIN
        IF NEW.modified_by_user = OLD.modified_by_user THEN
            SET NEW.modified_by_user = @users_id;
        END IF;
    END$$
    
    CREATE TRIGGER tg_TypicalTable_upd BEFORE UPDATE ON TypicalTable FOR EACH ROW
    BEGIN
    	CALL createRecord();
    END$$
    
    CREATE TRIGGER tg_TypicalTable_ins BEFORE INSERT ON TypicalTable FOR EACH ROW
    BEGIN
    	CALL modifyRecord();
    END$$
    
    DELIMITER ;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Actually, I thought I tested and it works, but it does not.

    When I run the script to create the database, I get the following error when creating the procedure:
    Code:
    #1193 - Unknown system variable 'created_by_user'
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Okay, The following seems to work. Question... Is what I am doing just wrong? Should I have a trigger call a procedure, or is that just too much? I have the same requirements on multiple tables, so normally it is advantageous to put duplicated script in one location, but is that not the case for SQL?

    Code:
    DELIMITER $$
    
    CREATE PROCEDURE `createRecord` (INOUT newCreated int,INOUT newModified int)
    BEGIN
        IF newCreated IS NULL OR NEW.newCreated = '' THEN
            SET newCreated = @users_id;
        END IF;
        IF newModified IS NULL OR newModified = '' THEN
            SET newModified = @users_id;
        END IF;
    END$$
    
    CREATE PROCEDURE `modifyRecord` (INOUT newModified int,INOUT oldModified int)
    BEGIN
        IF newModified = oldModified THEN
            SET newModified = @users_id;
        END IF;
    END$$
    
    CREATE TRIGGER tg_TypicalTable_upd BEFORE UPDATE ON TypicalTable FOR EACH ROW
    BEGIN
    	CALL createRecord(NEW.created_by_user,NEW.modified_by_user);
    END$$
    
    CREATE TRIGGER tg_TypicalTable_ins BEFORE INSERT ON TypicalTable FOR EACH ROW
    BEGIN
    	CALL modifyRecord(NEW.modified_by_user,OLD.modified_by_user);
    END$$
    
    DELIMITER ;
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    I would advice against calling procedures from within triggers.

    1.
    My general argument is that since triggers are so tightly coupled to the table they work on general triggers are very uncommon. Albeit it seems you have such a scenario in your design.

    2.
    But secondly there are a lot of limitations with calling procedures from within a trigger and there are performance issues.

    So I would advice against it and suggest that you define each trigger instead.
    /Stefan
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Thanks sr,

    I am sure you are right, but decided to test it. My results are below, next my database schema, and then my PHP test script.

    As a recap, for inserts, triggers increase execution time by about 15%, and the stored procedure adds another 10%. For updates, triggers might even improve speed, however, this might just be rounding error.

    How this effects me? I will use triggers because inserts are rare, and keeping audit trails out of the application has some advantages. It is easy to duplicate the stored procedures to the 10 or so tables, thus the benefit of using a stored procedure does not exceed the 10% performance hit and I will not use them.

    Code:
    Test	Count	Total Time	Average Time	Rank
    INSERT No Trigger No Prepare No Proc	188	44.324055671692	0.23576625357283	4.8223745914126%
    INSERT No Trigger Yes Prepare No Proc	157	35.312405347824	0.2249197792855	0%
    INSERT Yes Trigger No Prepare No Proc	178	46.368340969086	0.26049629757913	15.81742539791%
    INSERT Yes Trigger Yes Prepare No Proc	153	40.134891271591	0.26231955079471	16.628049177363%
    INSERT Yes Trigger No Prepare Yes Proc	160	44.298310518265	0.27686444073915	23.094750323276%
    INSERT Yes Trigger Yes Prepare Yes Proc	164	46.700424432755	0.28475868556558	26.604554952953%
    Total	1000	257.13842821121	0.25713842821121	14.324506732159%
    Test	Count	Total Time	Average Time	Rank
    UPDATE No Trigger No Prepare No Proc	169	50.633863449097	0.29960865946211	4.7884040105641%
    UPDATE No Trigger Yes Prepare No Proc	158	48.175404071808	0.30490762070764	6.6417205763478%
    UPDATE Yes Trigger No Prepare No Proc	177	52.310294151306	0.29553838503563	3.3648217890027%
    UPDATE Yes Trigger Yes Prepare No Proc	162	46.318677425385	0.28591776188509	0%
    UPDATE Yes Trigger No Prepare Yes Proc	180	52.336011171341	0.29075561761856	1.6920444891477%
    UPDATE Yes Trigger Yes Prepare Yes Proc	154	44.695423603058	0.29023002339648	1.5082174269128%
    Total	1000	294.46967387199	0.29446967387199	2.9910390772929%
    Code:
    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
    
    CREATE SCHEMA IF NOT EXISTS `triggerTest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
    USE `triggerTest` ;
    
    -- -----------------------------------------------------
    -- Table `triggerTest`.`myTriggerTable`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `triggerTest`.`myTriggerTable` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `data` VARCHAR(45) NULL ,
      `users_id` INT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `triggerTest`.`myNonTriggerTable`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `triggerTest`.`myNonTriggerTable` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `data` VARCHAR(45) NULL ,
      `users_id` INT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `triggerTest`.`myTriggerProcTable`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `triggerTest`.`myTriggerProcTable` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `data` VARCHAR(45) NULL ,
      `users_id` INT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- procedure InsertProc
    -- -----------------------------------------------------
    
    DELIMITER $$
    USE `triggerTest`$$
    CREATE PROCEDURE `triggerTest`.`InsertProc` (INOUT newID INT)
    BEGIN
        IF newID =NULL OR newID="" THEN
            SET newID = @users_id;
        END IF;
    END$$
    
    DELIMITER ;
    
    -- -----------------------------------------------------
    -- procedure UpdateProc
    -- -----------------------------------------------------
    
    DELIMITER $$
    USE `triggerTest`$$
    CREATE PROCEDURE `triggerTest`.`UpdateProc` (INOUT newID int,INOUT oldID int)
    BEGIN
        IF newID = oldID THEN
            SET newID = @users_id;
        END IF;
    END$$
    
    DELIMITER ;
    USE `triggerTest`;
    
    DELIMITER $$
    USE `triggerTest`$$
    
    
    CREATE TRIGGER tg_myTriggerTable_ins BEFORE INSERT ON myTriggerTable FOR EACH ROW
    BEGIN
        IF NEW.users_id IS NULL OR NEW.users_id = '' THEN
            SET NEW.users_id = @users_id;
        END IF;
    END$$
    
    USE `triggerTest`$$
    
    
    CREATE TRIGGER tg_myTriggerTable_upd BEFORE UPDATE ON myTriggerTable FOR EACH ROW
    BEGIN
        IF NEW.users_id = OLD.users_id THEN
            SET NEW.users_id = @users_id;
        END IF;
    END$$
    
    
    DELIMITER ;
    
    DELIMITER $$
    USE `triggerTest`$$
    
    
    CREATE TRIGGER tg_myTriggerProcTable_ins BEFORE INSERT ON myTriggerProcTable FOR EACH ROW
    BEGIN
    	CALL InsertProc(NEW.users_id);
    END$$
    
    USE `triggerTest`$$
    
    
    CREATE TRIGGER tg_myTriggerProcTable_upd BEFORE UPDATE ON myTriggerProcTable FOR EACH ROW
    BEGIN
    	CALL UpdateProc(NEW.users_id);
    END$$
    
    
    DELIMITER ;
    
    
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
    PHP Code:
    <?php
        
    class db {
            private static 
    $instance NULL;
            private function 
    __construct() {}   //Make private
            
    private function __clone(){}   //Make private
            
    public static function db() //Get instance of DB
            
    {
                if (!
    self::$instance)
                {
                    
    self::$instance = new PDO("mysql:host=localhost;dbname=triggerTest"'xxx''xxx');
                    
    self::$instance->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
                }
                return 
    self::$instance;
            }
        }

        
    set_time_limit(2000);    //change maximum execution time

        
    $c=500;
        
    $c2=1000;

        
    $data=array(
            
    0=>array('name'=>'INSERT No Trigger No Prepare No Proc','count'=>0,'time'=>0),
            
    1=>array('name'=>'INSERT No Trigger Yes Prepare No Proc','count'=>0,'time'=>0),
            
    2=>array('name'=>'INSERT Yes Trigger No Prepare No Proc','count'=>0,'time'=>0),
            
    3=>array('name'=>'INSERT Yes Trigger Yes Prepare No Proc','count'=>0,'time'=>0),
            
    4=>array('name'=>'INSERT Yes Trigger No Prepare Yes Proc','count'=>0,'time'=>0),
            
    5=>array('name'=>'INSERT Yes Trigger Yes Prepare Yes Proc','count'=>0,'time'=>0)
        );

        for (
    $j=1$j<=$c2$j++)
        {
            
    $start=microtime(true);
            
    $rand=rand(0,5);
            switch(
    $rand)
            {
                case 
    0:
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='INSERT INTO myNonTriggerTable (id, data, users_id) VALUES (NULL,'.$rand.',1)';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    1:
                    
    $sql ='INSERT INTO myNonTriggerTable (id, data, users_id) VALUES (NULL,?,1)';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
                    break;
                case 
    2:
                    
    db::db()->exec('SET @users_id=1');
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='INSERT INTO myTriggerTable (id, data) VALUES (NULL,'.$rand.')';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    3:
                    
    db::db()->exec('SET @users_id=1');
                    
    $sql ='INSERT INTO myTriggerTable (id, data) VALUES (NULL,?)';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
                    break;
                case 
    4:
                    
    db::db()->exec('SET @users_id=1');
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='INSERT INTO myTriggerProcTable (id, data) VALUES (NULL,'.$rand.')';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    5:
                    
    db::db()->exec('SET @users_id=1');
                    
    $sql ='INSERT INTO myTriggerProcTable (id, data) VALUES (NULL,?)';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
            }
            
    $data[$rand]['count']=$data[$rand]['count']+1;
            
    $data[$rand]['time']=$data[$rand]['time']+(microtime(true)-$start);       
        }

        
    $count=0;
        
    $time=0;
        foreach(
    $data as $key=>$test)
        {
            
    $data[$key]['average']=$test['time']/$test['count'];
            
    $count=$count+$data[$key]['count'];
            
    $time=$time+$data[$key]['time'];
        }
        
    $average=$time/$count;

        
    $minimum=min(array_map(function($details) {return $details['average'];}, $data));
        
    $rank=100*($average-$minimum)/$minimum;

        echo(
    '<table><tr><th>Test</th><th>Count</th><th>Total Time</th><th>Average Time</th><th>Rank</th></tr>');
        foreach(
    $data as $key=>$test) {
            
    $data[$key]['rank']=100*($data[$key]['average']-$minimum)/$minimum;
            echo(
    "<tr><td>{$test['name']}</td><td>{$test['count']}</td><td>{$test['time']}</td><td>{$data[$key]['average']}</td><td>{$data[$key]['rank']}%</td></tr>");
        }
        echo(
    "<tr><td>Total</td><td>{$count}</td><td>{$time}</td><td>{$average}</td><td>{$rank}%</td></tr></table>");

        
    $data=array(
            
    0=>array('name'=>'UPDATE No Trigger No Prepare No Proc','count'=>0,'time'=>0),
            
    1=>array('name'=>'UPDATE No Trigger Yes Prepare No Proc','count'=>0,'time'=>0),
            
    2=>array('name'=>'UPDATE Yes Trigger No Prepare No Proc','count'=>0,'time'=>0),
            
    3=>array('name'=>'UPDATE Yes Trigger Yes Prepare No Proc','count'=>0,'time'=>0),
            
    4=>array('name'=>'UPDATE Yes Trigger No Prepare Yes Proc','count'=>0,'time'=>0),
            
    5=>array('name'=>'UPDATE Yes Trigger Yes Prepare Yes Proc','count'=>0,'time'=>0)
        );

        for (
    $j=1$j<=$c2$j++)
        {
            
    $start=microtime(true);
            
    $rand=rand(0,5);
            switch(
    $rand)
            {
                case 
    0:
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='UPDATE myNonTriggerTable SET data='.$rand.', users_id=2 WHERE id=1';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    1:
                    
    $sql ='UPDATE myNonTriggerTable SET data=?, users_id=2 WHERE id=1';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
                    break;
                case 
    2:
                    
    db::db()->exec('SET @users_id=1');
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='UPDATE myNonTriggerTable SET data='.$rand.' WHERE id=1';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    3:
                    
    db::db()->exec('SET @users_id=1');
                    
    $sql ='UPDATE myNonTriggerTable SET data=? WHERE id=1';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
                    break;
                case 
    4:
                    
    db::db()->exec('SET @users_id=1');
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='UPDATE myNonTriggerTable SET data='.$rand.' WHERE id=1';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    5:
                    
    db::db()->exec('SET @users_id=1');
                    
    $sql ='UPDATE myNonTriggerTable SET data=? WHERE id=1';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
            }
            
    $data[$rand]['count']=$data[$rand]['count']+1;
            
    $data[$rand]['time']=$data[$rand]['time']+(microtime(true)-$start);       
        }

        
    $count=0;
        
    $time=0;
        foreach(
    $data as $key=>$test)
        {
            
    $data[$key]['average']=$test['time']/$test['count'];
            
    $count=$count+$data[$key]['count'];
            
    $time=$time+$data[$key]['time'];
        }
        
    $average=$time/$count;

        
    $minimum=min(array_map(function($details) {return $details['average'];}, $data));
        
    $rank=100*($average-$minimum)/$minimum;

        echo(
    '<table><tr><th>Test</th><th>Count</th><th>Total Time</th><th>Average Time</th><th>Rank</th></tr>');
        foreach(
    $data as $key=>$test) {
            
    $data[$key]['rank']=100*($data[$key]['average']-$minimum)/$minimum;
            echo(
    "<tr><td>{$test['name']}</td><td>{$test['count']}</td><td>{$test['time']}</td><td>{$data[$key]['average']}</td><td>{$data[$key]['rank']}%</td></tr>");
        }
        echo(
    "<tr><td>Total</td><td>{$count}</td><td>{$time}</td><td>{$average}</td><td>{$rank}%</td></tr></table>");

    ?>

IMN logo majestic logo threadwatch logo seochat tools logo