Re: [Maria-discuss] ON DELETE/UPDATE/INSERT Triggers
Just to add another one into the mix (and I guess it may be related in some sense)... For ON DELETE triggers it currently fires per row. This impacts performance significantly in some situations when a lot of rows are processed. MSSQL gets around this by creating virtual tables, INSERTED & DELETED (in effect similar to the OLD / NEW tags) and the trigger executes only once... https://msdn.microsoft.com/en-us/library/ms191300.aspx Cheers, Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Federico Razzoli Sent: 12 June 2015 04:02 To: Geoff Montee; Roberto Spadim Cc: MariaDB discuss Subject: Re: [Maria-discuss] DELETE FROM RETURNING In the meanwhile, we can use ON DELETE triggers. But the request is a good idea, because it is a common operation when we want to move some rows to history tables. Federico -------------------------------------------- Gio 11/6/15, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] DELETE FROM RETURNING A: "Geoff Montee" <geoff.montee@gmail.com> Cc: "MariaDB discuss" <maria-discuss@lists.launchpad.net> Data: Giovedì 11 giugno 2015, 21:09 +1, good idea, today i execute an insert, and a delete 2015-06-11 16:07 GMT-03:00 Geoff Montee <geoff.montee@gmail.com>: Hi Rhys, On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:
" This might be better suited as a feature request on the MariaDB JIRA."
I got chastised for doing that before so I'm trying to play nice! ;-)
I don't know why you were chastised in the past, but I've added this to the JIRA: https://mariadb.atlassian.net/browse/MDEV-8307 It sure sounds like a useful feature to me. Thanks for the suggestion! Geoff _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarial - Software ERPEng. Automação e Controle -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
DB2 goes even beyond. When you do INSERT/DELETE/UPDATE, you can SELECT from these tables: OLD - the old rows, unmodified NEW - the new rows, before any AFTER trigger or foreign key is executed FINAL - the final rows, after triggers and foreign keys are executed -------------------------------------------- Ven 12/6/15, Rhys Campbell <Rhys.Campbell@tradingscreen.com> ha scritto: Oggetto: RE: [Maria-discuss] ON DELETE/UPDATE/INSERT Triggers A: "Federico Razzoli" <federico_raz@yahoo.it>, "Geoff Montee" <geoff.montee@gmail.com>, "Roberto Spadim" <roberto@spadim.com.br> Cc: "MariaDB discuss" <maria-discuss@lists.launchpad.net> Data: Venerdì 12 giugno 2015, 10:55 Just to add another one into the mix (and I guess it may be related in some sense)... For ON DELETE triggers it currently fires per row. This impacts performance significantly in some situations when a lot of rows are processed. MSSQL gets around this by creating virtual tables, INSERTED & DELETED (in effect similar to the OLD / NEW tags) and the trigger executes only once... https://msdn.microsoft.com/en-us/library/ms191300.aspx Cheers, Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Federico Razzoli Sent: 12 June 2015 04:02 To: Geoff Montee; Roberto Spadim Cc: MariaDB discuss Subject: Re: [Maria-discuss] DELETE FROM RETURNING In the meanwhile, we can use ON DELETE triggers. But the request is a good idea, because it is a common operation when we want to move some rows to history tables. Federico -------------------------------------------- Gio 11/6/15, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] DELETE FROM RETURNING A: "Geoff Montee" <geoff.montee@gmail.com> Cc: "MariaDB discuss" <maria-discuss@lists.launchpad.net> Data: Giovedì 11 giugno 2015, 21:09 +1, good idea, today i execute an insert, and a delete 2015-06-11 16:07 GMT-03:00 Geoff Montee <geoff.montee@gmail.com>: Hi Rhys, On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:
" This might be better suited as a feature request on the MariaDB JIRA."
I got chastised for doing that before so I'm
trying to play nice! ;-)
I don't know why you were chastised in the past, but I've added this to the JIRA: https://mariadb.atlassian.net/browse/MDEV-8307 It sure sounds like a useful feature to me. Thanks for the suggestion! Geoff _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarial - Software ERPEng. Automação e Controle -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Yep, MSSQL does that too unless I misunderstand you. Rhys -----Original Message----- From: Federico Razzoli [mailto:federico_raz@yahoo.it] Sent: 12 June 2015 11:37 To: Geoff Montee; Roberto Spadim; Rhys Campbell Cc: MariaDB discuss Subject: RE: [Maria-discuss] ON DELETE/UPDATE/INSERT Triggers DB2 goes even beyond. When you do INSERT/DELETE/UPDATE, you can SELECT from these tables: OLD - the old rows, unmodified NEW - the new rows, before any AFTER trigger or foreign key is executed FINAL - the final rows, after triggers and foreign keys are executed -------------------------------------------- Ven 12/6/15, Rhys Campbell <Rhys.Campbell@tradingscreen.com> ha scritto: Oggetto: RE: [Maria-discuss] ON DELETE/UPDATE/INSERT Triggers A: "Federico Razzoli" <federico_raz@yahoo.it>, "Geoff Montee" <geoff.montee@gmail.com>, "Roberto Spadim" <roberto@spadim.com.br> Cc: "MariaDB discuss" <maria-discuss@lists.launchpad.net> Data: Venerdì 12 giugno 2015, 10:55 Just to add another one into the mix (and I guess it may be related in some sense)... For ON DELETE triggers it currently fires per row. This impacts performance significantly in some situations when a lot of rows are processed. MSSQL gets around this by creating virtual tables, INSERTED & DELETED (in effect similar to the OLD / NEW tags) and the trigger executes only once... https://msdn.microsoft.com/en-us/library/ms191300.aspx Cheers, Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Federico Razzoli Sent: 12 June 2015 04:02 To: Geoff Montee; Roberto Spadim Cc: MariaDB discuss Subject: Re: [Maria-discuss] DELETE FROM RETURNING In the meanwhile, we can use ON DELETE triggers. But the request is a good idea, because it is a common operation when we want to move some rows to history tables. Federico -------------------------------------------- Gio 11/6/15, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] DELETE FROM RETURNING A: "Geoff Montee" <geoff.montee@gmail.com> Cc: "MariaDB discuss" <maria-discuss@lists.launchpad.net> Data: Giovedì 11 giugno 2015, 21:09 +1, good idea, today i execute an insert, and a delete 2015-06-11 16:07 GMT-03:00 Geoff Montee <geoff.montee@gmail.com>: Hi Rhys, On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:
" This might be better suited as a feature request on the MariaDB JIRA."
I got chastised for doing that before so I'm trying to play nice! ;-)
I don't know why you were chastised in the past, but I've added this to the JIRA: https://mariadb.atlassian.net/browse/MDEV-8307 It sure sounds like a useful feature to me. Thanks for the suggestion! Geoff _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarial - Software ERPEng. Automação e Controle -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, You can also use FlexCDC. It of course can capture changes from all statements via the binlog. Flexviews can also be used for "as of" queries. If you have 30 days of log data collected, you can run a query (even with inner joins) and see the results as they appeared at any time in last 30 days (of course it will take a bit if you have lots of changes), and of course you could roll a view forward to now that was created or refreshed weeks ago, as long as you have the log tables populated since it was created or last refreshed. There is an article on it in the KB. Note: flashback (as of) queries are not in trunk but will be this weekend. -justin Sent from my iPhone
On Jun 12, 2015, at 1:55 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:
Just to add another one into the mix (and I guess it may be related in some sense)...
For ON DELETE triggers it currently fires per row. This impacts performance significantly in some situations when a lot of rows are processed. MSSQL gets around this by creating virtual tables, INSERTED & DELETED (in effect similar to the OLD / NEW tags) and the trigger executes only once...
https://msdn.microsoft.com/en-us/library/ms191300.aspx
Cheers,
Rhys
-----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Federico Razzoli Sent: 12 June 2015 04:02 To: Geoff Montee; Roberto Spadim Cc: MariaDB discuss Subject: Re: [Maria-discuss] DELETE FROM RETURNING
In the meanwhile, we can use ON DELETE triggers. But the request is a good idea, because it is a common operation when we want to move some rows to history tables.
Federico
-------------------------------------------- Gio 11/6/15, Roberto Spadim <roberto@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] DELETE FROM RETURNING A: "Geoff Montee" <geoff.montee@gmail.com> Cc: "MariaDB discuss" <maria-discuss@lists.launchpad.net> Data: Giovedì 11 giugno 2015, 21:09
+1, good idea, today i execute an insert, and a delete 2015-06-11 16:07 GMT-03:00 Geoff Montee <geoff.montee@gmail.com>: Hi Rhys,
On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell
<Rhys.Campbell@tradingscreen.com> wrote:
" This might be better suited as a feature request on the MariaDB JIRA."
I got chastised for doing that before so I'm trying to play nice! ;-)
I don't know why you were chastised in the past, but I've added this
to the JIRA:
https://mariadb.atlassian.net/browse/MDEV-8307
It sure sounds like a useful feature to me. Thanks for the suggestion!
Geoff
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERPEng. Automação e Controle
-----Segue allegato-----
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Federico Razzoli -
Justin Swanhart -
Rhys Campbell