Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
16.4k views
in Technique[技术] by (71.8m points)

sql - Can I define multiple events in one Trigger declaration in mysql?

Here is my requirement, I have a mysql table on which any change (insert/delete/update) should be handled in exactly same way. According to mysql documentation create trigger syntax is as follows:

    CREATE
     [DEFINER = { user | CURRENT_USER }]
     TRIGGER trigger_name
     trigger_time **trigger_event**
     ON tbl_name FOR EACH ROW
     trigger_body

When I'm trying to put more than one event, its throwing syntax error. One solution is I can write one procedure and 3 triggers (one for each event) and call the same procedure from all the triggers.

Is there any sophisticated solution for this ??

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

No. Only one "event" is supported. The trigger has to be either BEFORE or AFTER one of INSERT, UPDATE, DELETE.

If you have lots of logic that is "shared", you could write a procedure, and call the procedure from the body of the trigger.

The sophisticated solution to this is to use a different DBMS. Otherwise, you have to work within the confines of what MySQL supports.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...