CODEWITHSUNDEEP
mysqltriggers
Dominique Altrez
Dominique Altrez

Reputation: 123

Trigger Prevent to INSERT Data Without Raising Error

as mentioned in the title above, I want to create a Trigger (MySQL specifically) to prevent the data to be inserted without raising or throwing errors because it will cause transaction rollback.

Here's a sample of trigger I made:

    BEGIN
        IF NEW.running_test_id in (
            select A.running_test_id
            From running_subtests A
            where (NEW.running_test_id = A.running_test_id and NEW.subtest_id = A.subtest_id)
        ) THEN
           SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Duplicate in running test and subtest choosen';
        END IF;
    END

I've tried using warning (class = '01') but it still lets the data be inserted. Is it still possible to achieve this?

Upvotes: 0

Views: 247

Answers (1)

matigo
matigo

Reputation: 1461

The only consistent way I’ve been able to accomplish this within a trigger is to use a “soft-delete” field in the table. When the data is invalid, I issue a:

   SET deleted = 'Y';

Depending on the table, a follow-up or scheduled process will remove those soft-deleted records.

Upvotes: 0

Related Questions