How can I write an update trigger between two tables

Question

0.00/5 (No votes)

See more:

i have two tables one is called defaulters and users table .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 a zero value in debt_amount.
database link

What I have tried:

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;

this what i have tried and its is giving an error

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

Posted 26-May-19 21:08pm

harristars

Updated 27-May-19 2:48am

v9

Add a Solution

Comments

phil.o 27-May-19 5:12am

Please also provide the error message.

harristars 27-May-19 5:20am

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AFTER insert on defaulters
FOR EACH ROW
BEGIN
IF ( EXISTS (
' at line 2

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2019-05-26T23:32:00

Solution 1

Try to remove the semicolon ; at the end of the SELECT statement:

SQL

IF ( EXISTS (
   SELECT DISTINCT `KRAPIN` FROM defaulters WHERE `Debt_Amount` = 0
)

You only need semicolons if your are issuing several statements next to each-other. Here the SELECT clause is taken as an input argument to the EXISTS statement.

Source: 13.2.11.6 Subqueries with EXISTS or NOT EXISTS | MySQL 8.0 Reference Manual[^]

Posted 26-May-19 23:32pm

phil.o

Comments

harristars 27-May-19 5:40am

even after removing semicolon i get the same error.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AFTER insert on defaulters
FOR EACH ROW
BEGIN
IF ( EXISTS (
' at line 2

phil.o 27-May-19 5:48am

Remove the second AFTER insert on `defaulters`.

harristars 27-May-19 5:54am

i have removed but its giving me this error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 10

phil.o 27-May-19 5:56am

Please see my solution and apply it to specified line.
I kind of get the impression that you do not try to understand the error messages.

harristars 27-May-19 6:04am

i have included the DELIMITER // at the beginning and also at the END // and now i am getting the another
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE KRAPIN = NEW.KRAPIN;
END IF;
END' at line 11

phil.o 27-May-19 6:06am

Just remove all semicolons.

harristars 27-May-19 7:10am

my trigger is not updating. right now i dont have an error but its not updating the column that it is suppose to update.what could be the problem.

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

harristars · Answer 2 · 2019-05-27T02:49:00

this is working perfect

DELIMITER //
CREATE TRIGGER `after_update_debt_defaulters` AFTER UPDATE ON `defaulters`
 FOR EACH ROW BEGIN
       IF ( EXISTS (
            SELECT DISTINCT `KRAPIN` FROM defaulters WHERE defaulters.Debt_Amount = '0'
           )   
       )
        THEN
            UPDATE users 
            SET users.debtor = '0'
            WHERE KRAPIN = NEW.KRAPIN;
        END IF;
END