Database-Trigger to Delete row where condition True:

Question

I need trigger That Deletes row where in table paym both columns table1 and table2 are not empty.

Example in tables below: table: paym

 ID        username        table1        Table2  
+-------+-------------+-------------+-----------+
|   1   |  John       |  Value      |    Value  |
+-------+-------------+-------------+-----------+
|   2   |  Alex       |  Null       |    Null   |
+-------+-------------+-------------+-----------+

Condition is True: After Deleted row:

 ID        username        table1        Table2  
+-------+-------------+-------------+-----------+
|   2   |  Alex       |  Null       |    Null   |
+-------+-------------+-------------+-----------+

My attemp is: (Not working)

CREATE trigger DeleteROW
AFTER UPDATE ON paym
FOR EACH ROW
    BEGIN
        IF (NEW.table1 IS NOT NULL AND NEW.table2 IS NOT NULL) THEN
          DELETE
    FROM
        paym WHERE table1 and table2 IS NOT NULL ;
        END IF;
    END

I don't think you can do that in a before update or after update trigger. — DanB
– DanB, Commented Apr 10, 2019 at 18:42

Usagi Miyamoto · Accepted Answer · 2019-04-11 13:37:16Z

2

A trigger cannot modify the table it is running on.

You should create a stored procedure to handle this, and call that instead of the DELETE command...

answered Apr 11, 2019 at 13:37

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database-Trigger to Delete row where condition True:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related