MySQL AFTER INSERT Trigger not inserting into original table

Question

So basically I have a MySQL after insert trigger which should listen for insert operation, after a new row has been inserted in my monetaryTransactions table, the trigger should check whether the date of the new insert is smaller than the last date present in my other deposits table, and if that date is bigger (i.e newer), it should ALSO insert the record in the deposits table

This is the trigger itself ->

DELIMITER //

CREATE TRIGGER lv_deps_trigger
    AFTER INSERT
    ON MonetaryTransactions FOR EACH ROW
BEGIN
DECLARE ftdInt tinyint(1);
DECLARE agentName varchar(40);
DECLARE businessUnit varchar(40);
DECLARE parsedUnit varchar(40);
DECLARE depDate DATETIME;
DECLARE payment varchar(255);

IF (NEW.FirstTimeDeposit = 'false') THEN
SET ftdInt = 0;
ELSE 
SET ftdInt = 1;
END IF;

SELECT FullName FROM users WHERE SystemUserId = NEW.MTTransactionOwner INTO agentName;
SELECT `Bu Name` FROM users WHERE SystemUserId = NEW.MTTransactionOwner INTO businessUnit;
SELECT ApprovedOn FROM deposits ORDER BY ApprovedOn desc LIMIT 1 INTO depDate;

IF (businessUnit LIKE '%dummy%') THEN
SET parsedUnit = 'dummy';
ELSE 
SET parsedUnit = 'dummy';
END IF;

CALL processorFetcher(NEW.new_paymentprocessor, @AttrValue);
SELECT @AttrValue INTO payment;

IF (depDate < NEW.Lv_ApprovedOn ) THEN
    IF (NEW.Lv_name IN('Deposit','Withdrawal')) THEN
        INSERT INTO deposits(TPAccountID,Brand,AgentName,ApprovedOn,Amount,PaymentMethod,TransactionType,Department,FirstTimeDeposit) VALUES(NEW.TPAccountID, NEW.Department, agentName, NEW.Lv_ApprovedOn, NEW.Lv_Amount, payment, NEW.Lv_name, parsedUnit, ftdInt);
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not a wd/dp';
    END IF;
ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nothing to insert';
END IF;

END; //

DELIMITER ;

Basically, what it does right now is to check for the date, and only if that depDate < NEW.Lv_ApprovedOn condition returns true it will insert the record in both the deposits table and the monetarytransactions one. If the condition returns false, it will just trigger the else statement (nothing to insert) and it won't insert the record in the MonetaryTransactions.

This is indeed the logical behaviour I assume, however, I am unsure on how exactly I need to rework this so that IF the date is newer, it'll insert the record in both tables, if it's older, it will insert it only in the monetaryTransactions table.

I have attempted to just put an INSERT INTO monetarytransactions... in the ELSE condition but as expected it throws an error that I am not allowed to do that.

Any advice is welcome!

Answer 1

in Your case it would be more suitable to use BEFORE INSERT as it would let the INSERT query in the original table to proceed even if the "IF" statement is false, which is exactly Your case, and of course if it is getting in the IF statement it would INSERT into the original table and the trigger would do its work

DELIMITER //

CREATE TRIGGER lv_deps_trigger
    BEFORE INSERT
    ON lvr_MonetaryTransactions FOR EACH ROW
BEGIN
DECLARE ftdInt tinyint(1);
DECLARE agentName varchar(40);
DECLARE businessUnit varchar(40);
DECLARE parsedUnit varchar(40);
DECLARE depDate DATETIME;
DECLARE payment varchar(255);

IF (NEW.Lv_FirstTimeDeposit = 'false') THEN
SET ftdInt = 0;
ELSE 
SET ftdInt = 1;
END IF;

SELECT FullName FROM lvr_users WHERE SystemUserId = NEW.Lv_MTTransactionOwner INTO agentName;
SELECT `Bu Name` FROM lvr_users WHERE SystemUserId = NEW.Lv_MTTransactionOwner INTO businessUnit;
SELECT ApprovedOn FROM dep0sits ORDER BY ApprovedOn desc LIMIT 1 INTO depDate;

IF (businessUnit LIKE '%Conversion%') THEN
SET parsedUnit = 'Conversion';
ELSE 
SET parsedUnit = 'Retention';
END IF;

CALL processorFetcher(NEW.new_paymentprocessor, @AttrValue);
SELECT @AttrValue INTO payment;


IF (depDate < NEW.Lv_ApprovedOn ) THEN
    IF (NEW.Lv_name IN('Deposit','Withdrawal')) THEN
        INSERT INTO dep0sits(TPAccountID,Brand,AgentName,ApprovedOn,Amount,PaymentMethod,TransactionType,Department,FirstTimeDeposit) VALUES(NEW.TPAccountID, NEW.Department, agentName, NEW.Lv_ApprovedOn, NEW.Lv_Amount, payment, NEW.Lv_name, parsedUnit, ftdInt);
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not a wd/dp';
    END IF;
ELSE
    SET NEW.Lv_monetarytransactionId = NEW.Lv_monetarytransactionId;
    SET NEW.CreatedOn = NEW.CreatedOn;
    SET NEW.Lv_name = NEW.Lv_name;
    SET NEW.lv_type = NEW.lv_type;
    SET NEW.lv_accountid = NEW.lv_accountid;
    SET NEW.Lv_Amount = NEW.Lv_Amount;
    SET NEW.Lv_ApprovedOn = NEW.Lv_ApprovedOn;
    SET NEW.Lv_FirstTimeDeposit = NEW.Lv_FirstTimeDeposit;
    SET NEW.CurrencyName = NEW.CurrencyName;
    SET NEW.Lv_internalcomment = NEW.Lv_internalcomment;
    SET NEW.Lv_MTTransactionOwner = NEW.Lv_MTTransactionOwner;
    SET NEW.lv_tpaccountid = NEW.lv_tpaccountid;
    SET NEW.TPAccountID = NEW.TPAccountID;
    SET NEW.Lv_TransactionApproved = NEW.Lv_TransactionApproved;
    SET NEW.Lv_USDValue = NEW.Lv_USDValue;
    SET NEW.new_paymentprocessor = NEW.new_paymentprocessor;
    SET NEW.Department = NEW.Department;
END IF;

END; //

DELIMITER ;