CODEWITHSUNDEEP
mysqlsqldatabase-trigger
Faiyaj
Faiyaj

Reputation: 318

How to change a field value of the same table on insert using trigger (MySQL)

I have an 'example_table' and it has several fields. I want to change the 'example_field' value on insert into this same table when example_field='Faiyaj' AND example_field_two Like '096%' then i want to set the example_field = 'Faiyaj11'.

Here is my migration table (UP function): 

public function up()
{
   $this->create('example_table', function (Blueprint $table) {

       $table->bigIncrements('id');
       $table->integer('user_id')->unsigned();
       $table->text(description, 1600);
       $table->string('example_field');
       $table->string('example_field_two');
       $table->timestamp('created_at');

   });


   $insertTrigger = "CREATE TRIGGER example_table_generator_insert AFTER INSERT ON example_table
FOR EACH ROW
BEGIN
IF EXISTS(SELECT * FROM example_table WHERE example_field='Faiyaj' AND example_field_two Like '096%') THEN
UPDATE `example_table` SET `example_field` = 'Faiyaj11';
END IF;
END;
";


   $this->unprepared($insertTrigger);

}

I have used the above trigger in my migration table but it's not working and it is not inserting any value in the database. I have tried the trigger in several ways but failed. (BEFORE/AFTER INSERT)

Upvotes: 2

Views: 38

Answers (1)

Faiyaj
Faiyaj

Reputation: 318

I have solved the problem today. Here is the solution :

$insertTrigger = "CREATE TRIGGER example_table_generator_insert BEFORE INSERT ON example_table
FOR EACH ROW IF NEW.example_field = 'Faiyaj' AND NEW.example_field_two LIKE '096%' THEN SET NEW.example_field = 'Faiyaj11';
END IF;";

$this->unprepared($insertTrigger);

Upvotes: 2

Related Questions