Database trigger before insert and Calculations

Question

So I have these tables:

CREATE TABLE `chittytransactions` (
  `ChittyTransactionID` int(11) NOT NULL,
  `AuctionID` int(11) NOT NULL,
  `ChittyAccNo` int(11) DEFAULT NULL,
  `Date` datetime DEFAULT NULL,
  `Amount` double DEFAULT NULL,
  `Description` varchar(50) DEFAULT NULL,
  `TransRefence` varchar(50) DEFAULT NULL COMMENT 'Reference from actual Bank transaction',
  `TransStatus` tinyint(1) DEFAULT NULL COMMENT 'If Transaction Pending or Cleared',
  `ClearanceDate` datetime DEFAULT NULL,
  `PaymentMethod` int(1) DEFAULT NULL COMMENT '0- Cash, 1- bank transfer, 2- personal credit etc'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `chittyusers` (
  `ChittyAccNo` int(11) NOT NULL,
  `UserId` int(11) NOT NULL,
  `ChittyID` int(11) NOT NULL,
  `LatePaymentFee` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `UserId` int(11) NOT NULL,
  `UserName` varchar(45) NOT NULL,
  `UserNameVerified` tinyint(1) DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

What Im trying to do is check If a transaction date is the same as the cleared date in the chittytransaction table and if not add a late payment fee to the chittyusers table (preferable to be calculated based on each day a extra fee is added to late payment based on date). This should be done before and insert and I'm using a trigger. So far i have this:

ROP TRIGGER IF EXISTS chitty_beforet_trig;

DELIMITER ;;
CREATE TRIGGER chitty_beforet_trig BEFORE Insert ON chittytransaction
FOR EACH ROW
  BEGIN
  DECLARE `mainDate` datetime;
  DECLARE `claredDate` datetime;
  DECLARE `chitAccNo` INT(11);
  DECLARE `userId` INT(11);
  DECLARE `latePay` DOUBLE;
  DECLARE late TINYINT;
  DECLARE cursor1 CURSOR FOR SELECT ChittyAccNo FROM accounting.`chittyusers`;
  DECLARE cursor2 CURSOR FOR SELECT  `Date` FROM accounting.`chittyTransaction`; 
  DECLARE cursor3 CURSOR FOR SELECT  ClearanceDate FROM accounting.`chittyTransaction`;
  DECLARE cursor4 CURSOR FOR SELECT  UserId FROM accounting.`users`;

  OPEN cursor1;
  OPEN cursor2;
  OPEN cursor3;
  OPEN cursor4;

  FETCH cursor1 INTO chitAccNo;
  FETCH cursor2 INTO mainDate
  FETCH cursor3 INTO claredDate;
  FETCH cursor3 INTO userId;
    CASE
        WHEN claredDated <> mainDate THEN
            SET late ='1';
         ELSE
            SET late = '0';
         END;
    END CASE;

    IF late THEN
        UPDATE `chittyusers` SET LatePaymentFee = 50 WHERE UserId = userId;
    END IF;
 CLOSE cursor1;
 CLOSE cursor2;
 CLOSE cursor3;
 CLOSE cursor4;

 END;;
 DELIMITER;

I keep getting many different errors etc ad i don't know if I'm doing it right or not. Never used these before so its a little difficult. Can anyone tell me what I'm doing wrong please and any solutions would be appreciated.

Answer 1

My goodness, where to begin?

For starters, a row trigger should never query the table to which it is attached. (MySQL will allow this; many DBMSes won't.)

An INSERT row trigger always has access to the row being inserted via the NEW pseudorecord, which contains the same columns as the underlying table. The contents of NEW are initialized from the VALUES clause of the INSERT statement, and any changes you make to NEW will be reflected in the table row once the insert completes.

Another problem is that your cursor on chittyusers has an unconditional SELECT, which will grab every row from the table, not just the one you want. What you're actually doing is grabbing a nearly-random value for userId which probably has nothing to do with the one you want. You actually don't even need to use a cursor for this; a simple INSERT ... INTO with a WHERE clause will work better:

SELECT `UserId`
  INTO `userId`
  FROM chittyusers
  WHERE ChittyAccNo = NEW.ChittyAccNo;

Third, your CASE structure is superfluous; you can do exactly the same with a simple IF:

IF NEW.ClearanceDate <> NEW.`Date` THEN
  UPDATE `chittyusers`
    SET LatePaymentFee = 50
    WHERE UserId = userId;
END IF;

I'm not about to write the entire trigger for you, but these pointers should at least get the error messages down to a manageable number.