开发者

MySQL trigger which triggers on either INSERT or UPDATE?

开发者 https://www.devze.com 2023-03-23 06:12 出处:网络
Is there a way to create MySQL trigger which triggers on either UPDATE or INSERT? Something like CREATE TRIGGER t_apps_affected BEFORE INSERT OR UPDATE ...

Is there a way to create MySQL trigger which triggers on either UPDATE or INSERT?

Something like

CREATE TRIGGER t_apps_affected BEFORE INSERT OR UPDATE ...

开发者_StackOverflow社区Obviously, the above don't work. So, any workarounds without creating two separate triggers? I need this in order to update running counter on another table.


Unfortunately, there is no shorthand form - you must create multiple triggers - one for each event.

The doc says:

trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following:

INSERT: The trigger is activated whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

UPDATE: The trigger is activated whenever a row is modified; for example, through UPDATE statements.

DELETE: The trigger is activated whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. However, DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either. See Section 12.1.27, “TRUNCATE TABLE Syntax”.


While it is impossible to put a trigger on multiple events, you can define the two triggers to merely call another stored procedure and, with that, cut down on the amount of code you need to commit. Just create the separate triggers to do nothing but, say,

CALL update_counter();

and put all of your actual work into the procedure. The triggers would then be a simple

CREATE TRIGGER t_apps_affected BEFORE INSERT ON table
    FOR EACH ROW
BEGIN
CALL update_counter();
END;
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号