0

i have two tables one is called defaulters and users .defaulters has mypin and debt_amount and users has mypin and debtor .i want a trigger that update debtor in users table if mypin exist in users table where debt_amount=0 in defaulters .the trigger will input zero value in debtor if mypin in defaulters```` table has azerovalue indebt_amount```.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    debtor int(10),
    myPIN varchar(255),
    PRIMARY KEY (user_id)
)  ENGINE=INNODB;
CREATE TABLE defaulters (
    defaulters_id INT AUTO_INCREMENT,
    myPIN varchar(255),
    debt_amount varchar(255),
    PRIMARY KEY (defaulters_id)
)  ENGINE=INNODB; 

fiddle

this is what i have tried and it is giving me an error


CREATE TRIGGER `after_update_defaulters` AFTER INSERT ON `defaulters`
AFTER insert on defaulters
FOR EACH ROW 
BEGIN
       IF ( EXISTS (
            SELECT DISTINCT `KRAPIN` FROM defaulters WHERE `Debt_Amount` = 0;
           )      
    )
    THEN
        UPDATE users 
        SET debtor = 0;
        WHERE KRAPIN = NEW.KRAPIN;
     END IF;
END

  • Doesn't it work to update the 'other' table from values in the pseudo-table OLD which holds the value of the 'current' table? – Rick James May 26 at 0:01
  • what i mean is if debt_amount is zero then it should update the users table where mypin is equal to mypin in defaulters table where debt_amount is zero and it should update the debtor field in users table . – harristars May 26 at 8:24
  • That takes an IF statement with an UPDATE defaulters ... Make a stab at writing the TRIGGER code. – Rick James May 26 at 14:12
  • I totally don't have idea on how to write that trigger – harristars May 26 at 14:14
  • Start with examples in the documentation and stackoverflow – Rick James May 26 at 14:19

Your Answer

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

Browse other questions tagged or ask your own question.