CODEWITHSUNDEEP
mysqltriggers
chrisShick
chrisShick

Reputation: 1096

Mysql Triggers with if statements and variables

Okay, so I am needing to create an after insert trigger that needs to use an if elseif and another elseif. The if, elseif, elseif need to be based on query data obtained from 3 other database tables. I was wondering if there was a way I could do this without having to run all three queries at the beginning.

BEGIN
    SET @t3  = (SELECT team_id FROM team_trans WHERE key1 = NEW.trans_id LIMIT 1);
      SET @t2 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.STORE_ID AND team_assignments.type = 1);
      SET @t1 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.CUSTOMER_ID AND team_assignments.type = 0);
IF (@t3) THEN
               INSERT INTO team_trans (team_id, trans_id, type, misc_id) VALUES (@t3, NEW.id, 0, 1 );      
      ELSEIF (@t1) THEN
          INSERT INTO team_trans (team_id, trans_id, type, misc_id) VALUES (@t1, NEW.id, 0, 1 );
      ELSEIF (@t2) THEN
          INSERT INTO team_trans (team_id, trans_id, type, misc_id) VALUES (@t2, NEW.id, 0, 1 );
      END IF;
    END

Upvotes: 0

Views: 67

Answers (1)

wchiquito
wchiquito

Reputation: 16551

An option is:

...
BEGIN    
    /*
    SET @t3  = (SELECT team_id FROM team_trans WHERE key1 = NEW.trans_id LIMIT 1)//
    SET @t2 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.STORE_ID AND team_assignments.type = 1)//
    SET @t1 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.CUSTOMER_ID AND team_assignments.type = 0)//
    */    
    IF (SELECT @t3 := team_id
            FROM team_trans
            WHERE key1 = NEW.trans_id 
            LIMIT 1) THEN
        INSERT INTO team_trans (team_id, trans_id, type, misc_id)
        VALUES
        (@t3, NEW.id, 0, 1);      
    ELSEIF (SELECT @t1 := team_id
                    FROM team_assignments
                    WHERE team_assignments.misc_id = NEW.CUSTOMER_ID AND team_assignments.type = 0) THEN
        INSERT INTO team_trans (team_id, trans_id, type, misc_id)
        VALUES
        (@t1, NEW.id, 0, 1);
    ELSEIF (SELECT @t2 := team_id
                    FROM team_assignments
                    WHERE team_assignments.misc_id = NEW.STORE_ID AND team_assignments.type = 1) THEN
        INSERT INTO team_trans (team_id, trans_id, type, misc_id)
        VALUES
        (@t2, NEW.id, 0, 1);
    END IF;
END//
...

Upvotes: 1

Related Questions