MySQL Trigger to Prevent Insert Under Certain Conditions

MySQL Trigger to prevent INSERT under certain conditions

Based on this I'm not sure if it's possible to do it that way.

There's no support in MySQL's current
implementation of triggers to
voluntarily throw an exception and
abort the statement that spawned the
trigger.

The workaround I've found is to write
a BEFORE trigger to set one of the
not-NULL columns in the table to NULL,
thus violating its NOT NULL
constraint. This causes the statement
that spawned the trigger to be
aborted.

Creating trigger to prevent insert with condition from other table

try this

drop trigger if exists buku_dalam_pinjam_BI;
delimiter $$
CREATE  TRIGGER `buku_dalam_pinjam_BI`
BEFORE INSERT ON buku_dalam_pinjam
FOR EACH ROW BEGIN
    if exists (select 1 from anggota_dosen 
            where new.id_agt_dosen = anggota_dosen.id_agt_dosen and 
        anggota_dosen.ttl_proses_pinjam >=5) then
    signal sqlstate '45000' set message_text="error message";

    end if;
END $$
delimiter ;

MySQL Trigger: Prevent Insert by IF statement

You have a few missing commas and some made up sql and a missing delimter at the end.

DELIMITER $$

CREATE TRIGGER checkSubscribeTimeCourse 
BEFORE INSERT ON course_student
FOR EACH ROW
BEGIN

    IF (SELECT COUNT(*) FROM capacity c, course_capacity cc 
        WHERE c.cid = cc.cid 
        AND cc.cid = new.cid
        AND (c.end >= CURRENT_TIMESTAMP OR c.end IS NULL)
        AND (c.start <= CURRENT_TIMESTAMP OR c.start IS NULL)<1)
    THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error';
    END IF;

END$$

DELIMITER ;

Use a trigger to stop an insert or update

Try the SIGNAL syntax - https://dev.mysql.com/doc/refman/5.5/en/signal.html

create trigger agency_check
before insert on foo
for each row
begin
  if (new.agency < 1 or new.agency >5) then
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'your error message';
  end if 
end

EDIT

Updated based on popular comment below by Bill Karwin.

MySQL `BEFORE INSERT TRIGGER` how can I skip data insertion under condition?

Two solutions, both raise an error:

1. Call non-existent stored procedure - CALL non_existent_proc()
2. Use SIGNAL statement to raise the error (MySQL 5.5).

Example 1:

...
IF @found THEN
  CALL non_existent_proc();
END IF;
...

Example 2:

...
IF @found THEN
  SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Wrong data';`
END IF;
...

MySQL test before insert and cancel if condition is met

You have to use a signal

SIGNAL is the way to “return” an error. SIGNAL provides error
information to a handler, to an outer portion of the application, or
to the client. Also, it provides control over the error's
characteristics (error number, SQLSTATE value, message).

Thus you can have

CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
           ELSE
               SIGNAL SQLSTATE '01000'
                 SET MESSAGE_TEXT = 'Sorry cannot insert', MYSQL_ERRNO = 1000;
    ->     END IF;
    -> END;

Update: You might also want to take a look at the error messages list and choose a more appropriate error number.

How to abort INSERT operation in MySql trigger?

The comments in the mysql documentation about triggers suggest that there is no such feature in mysql. The best you can do is to create a separate table for your trigger errors, as suggested on the same page.

SQL: trigger to prevent inserting a row into a table based on a condition

I'd rearrange the logic here and:

1. Check if the driver has tested the vehicle, then
2. Check if the review is attempted before the testing end date for the vehicle (something you've left out).

In Oracle PL/SQL, which includes trigger code, you can't just SELECT. You have to SELECT INTO a variable. Then you can use the variable in your logic.

Equally important, when you SELECT INTO a variable the query can only return one result. Multiple rows will trigger the error you've encountered.

CREATE OR REPLACE TRIGGER review_check_validity
AFTER INSERT ON review
FOR EACH ROW
DECLARE
    testEnd DATE;
    vehicleTestCount NUMBER;
BEGIN

    SELECT COUNT(*)
      INTO vehicleTestCount
      FROM testing
      WHERE vehicle_id = :new.vehicle_id;

    IF vehicleTestCount = 0 THEN
      raise_application_error(-20000, 'Driver has never driven this vehicle');
    END IF;

    -- Assumes one test per driver per vehicle
    SELECT testing_end
      INTO testEnd
      FROM testing
      WHERE driver_no = :new.driver_no
        AND vehicle_id = :new.vehicle_id;

    IF :new.review_date < testEnd THEN
      raise_application_error(-20000, 'Review date cannot be before 
    testing end date');

    END IF;

END;
/

Finally, your table structure allows multiple tests of the same vehicle by the same driver. If it should allow this, then the review table should link to the testing table by testing_id rather than driver_no and vehicle_id.

---

Related Topics