esmaill23
6 months ago
331
5
Laravel

LARAVEL TRIGGER MIGRATION PROBLEM ON TEST ENVIRONMENT

Posted 6 months ago by esmaill23

hello i have created a laravel migration as like below. it works on migrate on mysql in product env but not work on sqlite in testing env . thank you :

public function up()
    {
        \Illuminate\Support\Facades\DB::unprepared('CREATE TRIGGER after_insert_price AFTER INSERT ON `movie_prices` FOR EACH ROW
            BEGIN
                IF((SELECT status from agreements where id = NEW.agreement_id) != 4 and (select sum(datediff(end_time,start_time)+1) from movie_prices where agreement_id = NEW.agreement_id) = (select datediff(end_time,start_time)+1 from agreements where id = NEW.agreement_id))
                THEN
                    UPDATE agreements SET status=2 where id = NEW.agreement_id;
                ELSEIF((SELECT status from agreements where id = NEW.agreement_id) != 4)
                THEN
                    UPDATE agreements SET status=3 where id = NEW.agreement_id;
                END IF;
            END');

        \Illuminate\Support\Facades\DB::unprepared('CREATE TRIGGER after_update_price AFTER UPDATE ON `movie_prices` FOR EACH ROW
            BEGIN
                IF((SELECT status from agreements where id = NEW.agreement_id) != 4 and (select sum(datediff(end_time,start_time)+1) from movie_prices where agreement_id = NEW.agreement_id) = (select datediff(end_time,start_time)+1 from agreements where id = NEW.agreement_id))
                THEN
                    UPDATE agreements SET status=2 where id = NEW.agreement_id;
                ELSEIF ((SELECT status from agreements where id = NEW.agreement_id) != 4)
                THEN
                    UPDATE agreements SET status=3 where id = NEW.agreement_id;
                END IF;
            END');

        \Illuminate\Support\Facades\DB::unprepared('CREATE TRIGGER after_delete_price AFTER DELETE ON `movie_prices` FOR EACH ROW
            BEGIN
                IF((SELECT status from agreements where id = OLD.agreement_id) != 4 AND (SELECT COUNT(*) FROM `movie_prices` WHERE agreement_id = OLD.agreement_id) = 0)
                THEN
                    UPDATE agreements SET status=1 where id = OLD.agreement_id;
                ELSEIF((SELECT status from agreements where id = OLD.agreement_id) != 4 and (select sum(datediff(end_time,start_time)+1) from movie_prices where agreement_id = OLD.agreement_id) = (select datediff(end_time,start_time)+1 from agreements where id = OLD.agreement_id))
                THEN
                    UPDATE agreements SET status=2 where id = OLD.agreement_id;
                ELSEIF ((SELECT status from agreements where id = OLD.agreement_id) != 4)
                THEN
                    UPDATE agreements SET status=3 where id = OLD.agreement_id;
                END IF;
            END');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        \Illuminate\Support\Facades\DB::unprepared('DROP TRIGGER `after_insert_price`');
        \Illuminate\Support\Facades\DB::unprepared('DROP TRIGGER `after_update_price`');
        \Illuminate\Support\Facades\DB::unprepared('DROP TRIGGER `after_delete_price`');
    }

raise this error on migrate on test env

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 near "IF": syntax error (SQL: CREATE TRIGGER after_insert_price AFTER INSERT ON `movie_prices` FOR EACH ROW
            BEGIN
                IF((SELECT status from agreements where id = NEW.agreement_id) != 4 and (select sum(datediff(end_time,start_time)+1) from movie_prices where agreement_id = NEW.agreement_id) = (select datediff(end_time,start_time)+1 from agreements where id = NEW.agreement_id))
                THEN
                    UPDATE agreements SET status=2 where id = NEW.agreement_id;
                ELSEIF((SELECT status from agreements where id = NEW.agreement_id) != 4)
                THEN
                    UPDATE agreements SET status=3 where id = NEW.agreement_id;
                END IF;
            END)

Please sign in or create an account to participate in this conversation.