Menu
I design one application to analyse counting frequentation data on different places in one city.
I have one data table (hereinafter referred to DT) for each counting site designed like this : startDate (DATETIME), dataCount(INT).
Every records contain the start date of counting and the number of accessed front the counter. the number of accesed is clear at each record. interval of record depends of the counter (hourly data in general).
I have one table (hereinafter referred to resumeDT) to summarize all my counting site : name, dateReference, location, description... AND dailyAvg.
I would like create one trigger of this type :
CREATE TRIGGER avgDT AFTER UPDATE,INSERT,DELETE ON DT
FOR EACH ROW UPDATE resumeDT SET avg= (SELECT AVG(tmp.sum)
FROM (SELECT sum(count) FROM DT GROUP BY DATE(date)) tmp)
WHERE dateReference="DT"
What is a bit troubling for me is that if I insert 500 values at one time, I will execute in addition 500 UPDATE. I would like execute my update query only once at the end.
I know that FOR EACH ROW is obligatory in the syntax of triggers but could I find one workaround to do what I want?
Maybe use trigger just to increment one mysql variable @DTChanged and call one external script which scan @DTChanged every second. if (@DTChanged!= 0 and lastDTChanged==@DTChanged) (no insert or update for 1s) => update of resumeDT
Have you got any advice or other solution?
Thanks in advance for your help.
861
No, you cannot create a trigger without the definer clause. As mysql documentation on create trigger says: If the DEFINER clause is omitted, the default definer is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW , then the trigger fires once for each row of the table that is affected by the triggering statement.
you can have many before triggers -- each modifying the :new values. That entire referenced thread was the proof that you cannot be sure the trigger is fired only once for each row!
Actually 'FOR EACH ROW' means for each of the matched rows that get either updated or deleted. In other words, we can say that trigger is not applied to each row, it just says to execute the trigger body for each affected table row.
The issue over triggers is a concept called RBAR (Row By Agonizing Row). Standard triggers (the most used, and simple to implement) are usually row based
https://www.sqlservercentral.com/Forums/Topic642789-338-1.aspx
Im not aware of statement based triggers for MySQL. But they do exist for other database engines (RDBMS) https://en.wikipedia.org/wiki/Database_trigger
Solutions on this case
1) On other databases, there's a standard feature called Materialized Views. The ones which updates automatically could fit in your needs. The issue is that for each updated done on the table(s), it will trigger a refresh that takes resources and time to finish, and that will impact on your transaction time. Some databases, like DB2, are able even to use the summarized data for queries not even referencing the summarized table, given its optimizer capabilities.
2) Regarding MySQL, given its openness, its possible to scope on the transaction / binary log, where all the changes made to database are recorded (This is also called Stream Processing). To scope on this information is called (Binary Log) Change Data Capture. The tool maxwells-daemon below allow you to capture and process the changes made so far. There's even another tools which simulates a Materialized View (flexviews)
http://maxwells-daemon.io/
https://mariadb.com/kb/en/library/flexviews/
Remember that the binary log will only 'valid'/'useful' after the transaction commit, so , there's probably a delay that will happen if your CDC do handle this. Better to check it out
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
