0

please pardon my English i am using language translator.
I have a table summary ,i want a TRIGGER to fire before insert on this table i.e summary and what i want is that, the TRIGGER should check if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert

`summary`
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 200   |   50        |  30  |
|22/17 | 100   |   10        |  80  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

`summary` // this is how i want it to be
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 300   |   150       |  30  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

my table code

CREATE TABLE `summary` (
  `id_` int(11) NOT NULL,
  `date_` date NOT NULL,
  `income_` text NOT NULL,
  `expenditure_` text NOT NULL,
  `other_` text NOT NULL,
  PRIMARY KEY(id) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `summary` 
(`id_`, `date_`, `income_`, `expenditure_`, `other_`)
VALUES
(1, '2017-12-22', 200, 50, 30),
(2, '2017-12-22', 100, 10, 80),
(3, '2017-12-23', 50, 100, 0);

This is what i have been trying so far, though am a Novist
trigger code

DELIMITER
$$
CREATE TRIGGER inc_trig 
BEFORE INSERT 
ON income_
FOR EaCH ROW
BEGIN
DECLARE income,expenditure,other INT;

SET income=1; SET expenditure=2; SET other=3;

IF( NEW.date_ != date_ )THEN

IF NEW.name_ = income THEN
INSERT INTO summary (date_,income)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = expenditure THEN
INSERT INTO summary (date_,expenditure_)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = other THEN
INSERT INTO summary (date_,other_)
VALUES (new.date_,new.amount);


ELSE(new.date_ = date_)THEN

IF NEW.name_ = income_ THEN
UPDATE summary SET income_ = income_ + new.amount);

ELSEIF NEW.name_ = expenditure THEN
UPDATE summary SET expenditure_ = expenditure_ + new.amount);

ELSEIF NEW.name_ = other THEN
UPDATE summary SET other_ = other_ + new.amount);


END IF;

END
$$

0

sorry fir bringing different code but am working with this wanted to make the question short...this trigger works but how to include the ON DUPLICATE KEY UPDATE giving me tough time and dosent work out

DELIMITER
$$
CREATE TRIGGER inc_trig 
BEFORE INSERT 
ON income_
FOR EaCH ROW
BEGIN
DECLARE fees,bus,uniform,c_uniform,sp_wear,pullover,car INT;

SET fees=1; SET bus=2; SET uniform=3; SET c_uniform=4; SET sp_wear=5; SET pullover=6; SET car=7;

IF NEW.income_name_ = fees THEN
INSERT INTO income_summary_monthly (date_,fees_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);

ELSEIF NEW.income_name_ = bus THEN
INSERT INTO income_summary_monthly (date_,bus_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);

ELSEIF NEW.income_name_ = uniform THEN
INSERT INTO income_summary_monthly (date_,uniform_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);

ELSEIF NEW.income_name_ = c_uniform THEN
INSERT INTO income_summary_monthly (date_,c_uniform_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);

ELSEIF NEW.income_name_ = sp_wear THEN
INSERT INTO income_summary_monthly (date_,sp_wear_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);

ELSEIF NEW.income_name_ = pullover THEN
INSERT INTO income_summary_monthly (date_,pullover_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);


ELSEIF NEW.income_name_ = car THEN
INSERT INTO income_summary_monthly (date_,c_a_r_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);


END IF;

END;
$$

0

"if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert" -- You have just specified what INSERT ... ON DUPLICATE KEY UPDATE ... does. Use that.

this trigger works but how to include the ON DUPLICATE KEY UPDATE giving me tough time and dosent work out

DELIMITER $$
CREATE TRIGGER inc_trig 
    BEFORE INSERT ON income_
FOR EACH ROW
BEGIN
    DECLARE fees,bus,uniform,c_uniform,sp_wear,pullover,car INT;

    SET fees=1; SET bus=2; SET uniform=3; SET c_uniform=4;
    SET sp_wear=5; SET pullover=6; SET car=7;

    IF NEW.income_name_ = fees THEN
    INSERT INTO income_summary_monthly
            (date_, fees_, month_, term_, schid_)
        VALUES
            (new.date_, new.amount_, new.month_, new.term_, new.schid_)
        ON DUPLICATE KEY UPDATE
            amount_ = VALUES(amount_);

// Or do you want
//          amount_ = amount_ + VALUES(amount_) ??

    ELSEIF NEW.income_name_ = bus THEN
    ...        
    END IF;

END  $$
DELIMITER ;

I am a bit lost since the original code does not look much like this version.

  • pls give me a code i dont understand – Joshua Sampson Dec 10 '17 at 20:13
  • It's almost same thing I only make the question short... – Joshua Sampson Dec 11 '17 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.