What does this MySQL trigger do?

I have two versions of the same DB. The original has code for triggers. The second is a copy of the original without the triggers.

With the original DB, the application fails to post data on submit, but data seems to post correctly to the copy.

Here's the trigger code:

DROP TRIGGER IF EXISTS `uat`.`updateConditionCCRDataObjectID`;
DELIMITER //
CREATE TRIGGER `uat`.`updateConditionCCRDataObjectID` BEFORE INSERT ON `uat`.`condition_details` 
 FOR EACH ROW BEGIN
	IF NEW.CCRDataObjectID = NULL OR NEW.CCRDataObjectID = '' THEN
		SELECT coalesce(MAX(id)+1,1) into @IDVal from condition_details;
		SET NEW.CCRDataObjectID = CONCAT('Comp-Condition-',@IDVal);
	END IF;
    END
//
DELIMITER ;

Code (markup):

Could someone explain what the trigger does and perhaps provide an opinion on whether it's necessary?

Thanks

 

Basically, when you insert a new row into the `condition_details` table, the trigger looks at the value of the inserted CCRDataObjectID column. If that value is NULL or empty '', it selects the max, non null (coalesce), id value and increments by 1. It puts this value into the CCRDataObjectID column.

My guess it that it is doing this to preserve some sort of manual incrementation and table association for each submitted row. Without seeing a lot more about the app and the database I couldn't tell you if this is required, but from my experience with apps, this is most likely essential, and shouldn't be removed.

 

Thanks much jesep.

I'm surprised that the code did not copy to new DBs with phpMyAdmin's copy function. Do you have any quick hints on how to copy the trigger code?

 

You need to run the trigger codes in a sql prompt. Just run exactly what you posted and it should go into the copied database.

If the code was originally failing on inserts you will need to debug what is failing.

 

The code contains both DB and table names such as `uat`.`updateConditionCCRDataObjectID`. Obviously for other DBs I'll have to change the DB name. However, is the DB name necessary? Could I just use `updateConditionCCRDataObjectID` without `uat`?

Thanks again.

 

Normally you would be fine without having the db name in the query. Just make sure you are using the correct database when you run the query.

If you are logged into a specific database via phpmyadmin, you should be fine.
Otherwise,

run

use myDbName;

 

Thanks Jestep. Now all I have to do is figure out why it wasn't working! The triggers may not be the problem.

 

A couple of other questions.

1. Is there a way to see the trigger code in phpMyAdmin?

2. Is there a way to remove the trigger code in phpMyAdmin?