CREATE TRIGGER AFTER 100 UPDATES UPDATE
UPDATE table4 SET col1 = col1 + 1 WHERE col1 = new_value INSERT INTO subject_table_audit VALUES (new_value, event, Now()) ĭELETE FROM table3 WHERE col1 = new_value The sp_subject_table_trigger stored procedure will handle the issuing of SQL statements for all the events which are associated with a trigger: DELIMITER $$ĬREATE PROCEDURE `sp_subject_table_trigger`(new_value VARCHAR(100), event VARCHAR(9)) These in turn will delegate the work to the same stored procedure, passing in whatever information is required: DELIMITER $$ĬREATE TRIGGER `subject_table_trigger_insert` AFTER INSERT on `subject_table`ĬALL sp_subject_table_trigger(NEW.Column, 'insert') ĬREATE TRIGGER `subject_table_trigger_update` AFTER UPDATE on `subject_table`ĬALL sp_subject_table_trigger(NEW.Column, 'update') ĬREATE TRIGGER `subject_table_trigger_delete` AFTER DELETE on `subject_table`ĬALL sp_subject_table_trigger(NEW.Column, 'delete') The way to get around these limitations is to combine triggers with stored procedures.ĭefine a trigger for each event that you want to bind to. The second issue is that there is no mechanism for determining the operation type from within a trigger. First, the combining of trigger events (INSERT, UPDATE, DELETE) is not permitted. There are a couple of reasons that the above statement will fail. INSERT INTO subject_table_audit values(xxx, delete) INSERT INTO subject_table_audit values(xxx, update)
INSERT INTO subject_table_audit values(xxx, insert)
CREATE TRIGGER AFTER 100 UPDATES HOW TO
If one were just learning how to write triggers, he or she might be tempted to define one as follows: CREATE TRIGGER multi_purpose_trigger AFTER INSERT, UPDATE, DELETE ON subject_table There are many reasons that DBAs would want to bind a trigger to more than one event for the same table. The INSERT, DELETE, or UPDATE keyword in the statement above is called the trigger event. In an INSERT operation like the one above, only the NEW value is pertinent. col_name refers to the new value to be inserted or an existing one after it is updated. col_name alias refers to an existing field before it is updated or deleted, whereas NEW. Within the context of a trigger, the OLD. Regarding the NEW prefix above, MySQL offers a way to compare the value that is about to be written to a table to the existing one. UPDATE table4 SET col1 = col1 + 1 WHERE col1 = NEW.col1 For instance, here is a trigger that performs three separate operations on three different tables based on the value being inserted into the subject table: delimiter |ĬREATE TRIGGER combo_trig BEFORE INSERT ON table1ĭELETE FROM table3 WHERE col1 = NEW.col1 What can be combined are statements within an individual trigger. Likewise, it is not possible to define triggers on individual table columns either. For instance, you cannot design a trigger to fire whenever a query either updates OR inserts data into a given table. Nonetheless, it is not possible to define multiple operations for a single trigger. As mentioned previously, a trigger can be defined to activate either before or after an INSERT, DELETE, or UPDATE statement.
With the latest MySQL Community Server being 5.5.27, it should come as no surprise that trigger support is less refined as those of some of the more senior RDBMS players. Support for triggers in MySQL was first introduced in version 5.0.2. Highlights include how to bind a trigger to multiple events and how to apply changes to the subject table from within a trigger. In doing so, we will examine some of the limitations of triggers as implemented in MySQL as well as ways to circumvent them. Today’s follow-up will demonstrate how stored procedures can be used in conjunction with triggers to enhance their effectiveness. In my The Wonderful (and not so Wonderful) Things about MySQL Triggers article, we saw how triggers can be useful in some contexts, and yet inferior to stored procedures in others.
0 kommentar(er)
