trigger after update to delete row on condition

Question

i want to create trigger after update to delete row on specific condition i did find similar question here but the code won't work with me.

This is what i try to do :

DELIMITER $$

CREATE TRIGGER delete_rejected_friendship
AFTER UPDATE
    ON friendship FOR EACH ROW
    BEGIN
        IF Update(RequestState)
        BEGIN
            DELETE FROM friendship WHERE RequestState = 'reject'
        END
   END
END$$
DELIMITER ;

This is the error which appears:

#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 'Update(RequestState) Begin DELETE FROM friendship WHERE Reque' at line 9`

so i have friendship table that have three columns userID , friendID and RequestState. this table represent friendship relationship and friendship request state. if RequestState= accept then they are friends and if RequestState = reject the row should be deleted.

Edit : if update(requeststate) for if the column = requeststate updated (changed from 'waiting' state to either 'accept' or 'reject' state) then the other condition in delete statement (WHERE RequestState = 'reject') to specify only change to 'reject' state

I don't think you can do this, q.v. [this answer](http://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert). Perhaps you can rework your logic? — Tim Biegeleisen, Feb 10 '16 at 07:49
Please give more explanation about the condition If Update(RequestState) — Shukri Gashi, Feb 10 '16 at 07:53
@TimBiegeleisen please do not correct casing, what if the error comes from there? Not that SQL is case sensitive, but anyhow, excepting indentation or empty lines, we should keep the original code when editing. — Cristik, Feb 10 '16 at 07:54
@Cristik I'm aware of this type of problem. I was only trying to make the problem more readable so other SO users can help. — Tim Biegeleisen, Feb 10 '16 at 07:56

Carlos Rodriguez · Accepted Answer · 2016-02-12T15:25:16.150

0

I think, You are using Update(RequestState) condition like PostgreSQL, in MySQL or MariaDB could be like that:

DELIMITER $$

CREATE TRIGGER delete_rejected_friendship
AFTER UPDATE
    ON friendship FOR EACH ROW
    BEGIN
        IF NEW.RequestState <> OLD.RequestState THEN
            BEGIN
                DELETE FROM friendship WHERE RequestState = 'reject';
            END;
        END IF;
   END;
$$
DELIMITER ;

edited Feb 12 '16 at 15:25

answered Feb 11 '16 at 13:05

Carlos Rodriguez

833
8
12

it's give me error (unexpected character ,(near ;) in delete statement line and END IF line. and if i delete the semecolon from these two line it will give 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 'END END IF END END' at line 8) @Carlos Rodriguez – showihd Feb 11 '16 at 22:00
@showihd Sorry, I edit the answer, delete the last `END` and add the `END;`, I run this query on mysql console and run fine, but, in Navicat gave me an error but, create the trigger fine, so verify when you run this query. – Carlos Rodriguez Feb 12 '16 at 15:26
thank you, the trigger created now. but when i run my update query to test the trigger it will give me this error :(#1442 - Can't update table 'friendship' in stored function/trigger because it is already used by statement which invoked this stored function/trigger) and this is the update query i tried: (UPDATE friendship SET RequestState = 'reject' WHERE UserID = 1 AND FriendID = 4;) – showihd Feb 12 '16 at 17:17
This need a revision from you part, because you can do delete when `RequestState='reject'`, so no need update for set `RequestState='reject'`, yo need delete in this instance, because, you execute the update, the update wait because this execute a trigger, this wait because this execute a delete... – Carlos Rodriguez Feb 13 '16 at 03:28
Thank you very much Carlos ! your answer really helpful. I solve the problem with SQL statement, i don't know why my brain immediately think of trigger when there is easier way!. thank you again. – showihd Feb 15 '16 at 02:44
@showihd no problem, you can mark my answer as the correct answer? Thanks. – Carlos Rodriguez Feb 15 '16 at 17:13

trigger after update to delete row on condition

1 Answers1