CODEWITHSUNDEEP
mysql
apr0vleptos
apr0vleptos

Reputation: 1

MySQL triggers check if insert is successfull or not

As mentioned in the title, i have created a trigger that catches when a new value is inserted in the 'candidate' table, and stores some information in the table 'history'. However, i would also like to store if the insertion is successful or not, but cannt quite seem to get it to work. I have tried:

DELIMITER $$
CREATE TRIGGER cand_i BEFORE INSERT ON candidate FOR EACH ROW
BEGIN
DECLARE action varchar(10);
IF(EXISTS(select username from candidate where username=NEW.username)) THEN
    SET action='Failed';
ELSE SET action='Success';
END IF;
INSERT INTO history (actiontype,actiondate,actionsuccess,actiontable,username)
VALUES ('Insert',now(),action,'Candidate',NEW.username);
END $$
DELIMITER ;

Any suggestions?

Upvotes: 0

Views: 1819

Answers (1)

Nick
Nick

Reputation: 147266

If you are using a non-transactional table (e.g. MyISAM) for the history table, you can achieve the result you want by using BEFORE and AFTER triggers as described under Original Answer. If both candidate and history are transactional tables (e.g. InnoDB), if the INSERT fails, the entire transaction - including the operation performed in the BEFORE trigger - gets rolled back, so this method will not work. In that case you need to use the AFTER trigger described below, and perform the INSERT to the history table prior to the INSERT to the candidate table e.g.

INSERT INTO history (actiontype,actiondate,actionsuccess,actiontable,username)
    VALUES ('Insert',now(),'Failed','Candidate','<username>');
INSERT INTO candidate (..., username, ...)
    VALUES (..., '<username>', ...);

Original Answer

Since you want to record the INSERT, regardless of whether it succeeded or not, you will need to implement this using two triggers. In the BEFORE trigger, simply create an entry with a failed status, and in the AFTER trigger, update the status to Success. Since AFTER triggers are only run if the row operation succeeds (manual), this will do what you want.

DELIMITER $$
CREATE TRIGGER cand_i BEFORE INSERT ON candidate FOR EACH ROW
BEGIN
INSERT INTO history (actiontype,actiondate,actionsuccess,actiontable,username)
VALUES ('Insert',now(),'Failed','Candidate',NEW.username);
END $$

CREATE TRIGGER cand_i_a AFTER INSERT ON candidate FOR EACH ROW
BEGIN
UPDATE history
SET actionsuccess = 'Success'
WHERE actiontype = 'Insert' AND actiontable = 'Candidate' AND username = NEW.username;
END $$
DELIMITER ;

Upvotes: 1

Related Questions