+----------------------+--------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ | TGGR_AGGR_INS_UNIQUE | INSERT | aggr | BEGIN DECLARE done boolean DEFAULT FALSE; DECLARE _id int; DECLARE cur CURSOR FOR SELECT AGGR_EDITINGADMINID id FROM aggr WHERE (AGGR_NAME = new.AGGR_NAME AND AGGR_ID <> COALESCE(new.AGGR_orgobjid,-1) AND AGGR_ID <> new.AGGR_id AND NOT AGGR_ID IN (SELECT orginal.AGGR_ID FROM aggr orginal LEFT JOIN aggr edited ON (edited.AGGR_ORGOBJID = orginal.AGGR_ID) WHERE (edited.AGGR_DELETEFLAG = 1 AND edited.AGGR_EDITINGADMINID = new.aggr_editingadminid) OR (orginal.AGGR_DELETEFLAG = 1 AND orginal.AGGR_EDITINGADMINID = new.aggr_editingadminid)) AND NOT AGGR_ID IN (SELECT orginal.AGGR_ID FROM aggr orginal LEFT JOIN aggr edited ON edited.AGGR_ORGOBJID = orginal.AGGR_ID WHERE (COALESCE(edited.AGGR_DELETEFLAG,0) = 0 AND edited.AGGR_EDITINGADMINID = new.aggr_editingadminid))) OR (-1 <> new.AGGR_orgobjid AND AGGR_ORGOBJID = new.aggr_orgobjid AND AGGR_ID <> new.aggr_id); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; uniqueloop: LOOP FETCH cur INTO _id; IF done THEN LEAVE uniqueloop; END IF; SET @err = CONCAT('SQL-20001: Unique constraint violated (name): TABLE: AGGR AdminID: #', COALESCE(_id,0), '#'); SIGNAL SQLSTATE '20001' SET MESSAGE_TEXT = @err, MYSQL_ERRNO = 20001; END LOOP uniqueloop; CLOSE cur; END | BEFORE | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | latin1_general_cs | | TGGR_AGGR_UP_UNIQUE | UPDATE | aggr | BEGIN DECLARE done boolean DEFAULT FALSE; DECLARE _id int; DECLARE cur CURSOR FOR SELECT AGGR_EDITINGADMINID id FROM AGGR WHERE (AGGR_NAME = new.AGGR_NAME AND AGGR_ID <> COALESCE(new.AGGR_orgobjid,-1) AND AGGR_ID <> new.AGGR_id AND NOT AGGR_ID IN (SELECT orginal.AGGR_ID FROM AGGR orginal LEFT JOIN AGGR edited ON (edited.AGGR_ORGOBJID = orginal.AGGR_ID) WHERE (edited.AGGR_DELETEFLAG = 1 AND COALESCE(edited.AGGR_EDITINGADMINID,0) = COALESCE(new.AGGR_editingadminid,0)) OR (orginal.AGGR_DELETEFLAG = 1 AND COALESCE(orginal.AGGR_EDITINGADMINID,0) = COALESCE(new.AGGR_editingadminid,0))) AND NOT AGGR_ID IN (SELECT edited.AGGR_ID FROM AGGR orginal LEFT JOIN AGGR edited ON edited.AGGR_ORGOBJID = orginal.AGGR_ID WHERE (COALESCE(edited.AGGR_DELETEFLAG,0) = 0 AND COALESCE(edited.AGGR_EDITINGADMINID,0) = COALESCE(new.AGGR_editingadminid,0) ))); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; uniqueloop: LOOP FETCH cur INTO _id; IF done THEN LEAVE uniqueloop; END IF; SET @err = CONCAT('SQL-20001: Unique constraint violated (name): TABLE: AGGR AdminID: #', COALESCE(_id,0), '#'); SIGNAL SQLSTATE '20001' SET MESSAGE_TEXT = @err, MYSQL_ERRNO = 20001; END LOOP uniqueloop; CLOSE cur; END | BEFORE | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | latin1_general_cs | +----------------------+--------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ 2 rows in set (0.02 sec) +----------------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation | +----------------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | TGGR_AGGR_INS_UNIQUE | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE OR REPLACE DEFINER=`root`@`%` TRIGGER TGGR_AGGR_INS_UNIQUE BEFORE INSERT ON aggr FOR EACH ROW BEGIN DECLARE done boolean DEFAULT FALSE; DECLARE _id int; DECLARE cur CURSOR FOR SELECT AGGR_EDITINGADMINID id FROM aggr WHERE (AGGR_NAME = new.AGGR_NAME AND AGGR_ID <> COALESCE(new.AGGR_orgobjid,-1) AND AGGR_ID <> new.AGGR_id AND NOT AGGR_ID IN (SELECT orginal.AGGR_ID FROM aggr orginal LEFT JOIN aggr edited ON (edited.AGGR_ORGOBJID = orginal.AGGR_ID) WHERE (edited.AGGR_DELETEFLAG = 1 AND edited.AGGR_EDITINGADMINID = new.aggr_editingadminid) OR (orginal.AGGR_DELETEFLAG = 1 AND orginal.AGGR_EDITINGADMINID = new.aggr_editingadminid)) AND NOT AGGR_ID IN (SELECT orginal.AGGR_ID FROM aggr orginal LEFT JOIN aggr edited ON edited.AGGR_ORGOBJID = orginal.AGGR_ID WHERE (COALESCE(edited.AGGR_DELETEFLAG,0) = 0 AND edited.AGGR_EDITINGADMINID = new.aggr_editingadminid))) OR (-1 <> new.AGGR_orgobjid AND AGGR_ORGOBJID = new.aggr_orgobjid AND AGGR_ID <> new.aggr_id); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; uniqueloop: LOOP FETCH cur INTO _id; IF done THEN LEAVE uniqueloop; END IF; SET @err = CONCAT('SQL-20001: Unique constraint violated (name): TABLE: AGGR AdminID: #', COALESCE(_id,0), '#'); SIGNAL SQLSTATE '20001' SET MESSAGE_TEXT = @err, MYSQL_ERRNO = 20001; END LOOP uniqueloop; CLOSE cur; END | utf8 | utf8_general_ci | latin1_general_cs | +----------------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) +---------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation | +---------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | TGGR_AGGR_UP_UNIQUE | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE OR REPLACE DEFINER=`root`@`%` TRIGGER TGGR_AGGR_UP_UNIQUE BEFORE UPDATE ON aggr FOR EACH ROW BEGIN DECLARE done boolean DEFAULT FALSE; DECLARE _id int; DECLARE cur CURSOR FOR SELECT AGGR_EDITINGADMINID id FROM AGGR WHERE (AGGR_NAME = new.AGGR_NAME AND AGGR_ID <> COALESCE(new.AGGR_orgobjid,-1) AND AGGR_ID <> new.AGGR_id AND NOT AGGR_ID IN (SELECT orginal.AGGR_ID FROM AGGR orginal LEFT JOIN AGGR edited ON (edited.AGGR_ORGOBJID = orginal.AGGR_ID) WHERE (edited.AGGR_DELETEFLAG = 1 AND COALESCE(edited.AGGR_EDITINGADMINID,0) = COALESCE(new.AGGR_editingadminid,0)) OR (orginal.AGGR_DELETEFLAG = 1 AND COALESCE(orginal.AGGR_EDITINGADMINID,0) = COALESCE(new.AGGR_editingadminid,0))) AND NOT AGGR_ID IN (SELECT edited.AGGR_ID FROM AGGR orginal LEFT JOIN AGGR edited ON edited.AGGR_ORGOBJID = orginal.AGGR_ID WHERE (COALESCE(edited.AGGR_DELETEFLAG,0) = 0 AND COALESCE(edited.AGGR_EDITINGADMINID,0) = COALESCE(new.AGGR_editingadminid,0) ))); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; uniqueloop: LOOP FETCH cur INTO _id; IF done THEN LEAVE uniqueloop; END IF; SET @err = CONCAT('SQL-20001: Unique constraint violated (name): TABLE: AGGR AdminID: #', COALESCE(_id,0), '#'); SIGNAL SQLSTATE '20001' SET MESSAGE_TEXT = @err, MYSQL_ERRNO = 20001; END LOOP uniqueloop; CLOSE cur; END | utf8 | utf8_general_ci | latin1_general_cs | +---------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec)