Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
is there any plan for supporting conditional trigger firing as in Oracle when specifying an optional WHEN clause in the trigger DDL syntax? Currently in Firebird, to omit trigger code execution, one can wrap the entire trigger body into an IF block, but the trigger still fires, but no PSQL code is executed. So the question is: Is it worth to think about conditional trigger firing or is trigger firing in Firebird that cheap, so we don't have to bother? With all the discussion, is it worth to create a JIRA issue for this feature request? Thanks, Thomas -- RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
07.11.2011 11:23, Thomas Steinmaurer wrote: With all the discussion, is it worth to create a JIRA issue for this feature request? I don't mind :-) Dmitry -- RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On Mon, 10 Oct 2011 08:17:19 +0200, Thomas Steinmaurer t...@iblogmanager.com wrote: Hello, is there any plan for supporting conditional trigger firing as in Oracle when specifying an optional WHEN clause in the trigger DDL syntax? Currently in Firebird, to omit trigger code execution, one can wrap the entire trigger body into an IF block, but the trigger still fires, but no PSQL code is executed. So the question is: Is it worth to think about conditional trigger firing or is trigger firing in Firebird that cheap, so we don't have to bother? I think that the Oracle WHEN-clause is simply syntactic sugar for wrapping the entire body in an IF-condition. I don't think there would be much difference performance-wise. Mark -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On Mon, 10 Oct 2011 08:17:19 +0200, Thomas Steinmaurer t...@iblogmanager.com wrote: Hello, is there any plan for supporting conditional trigger firing as in Oracle when specifying an optional WHEN clause in the trigger DDL syntax? Currently in Firebird, to omit trigger code execution, one can wrap the entire trigger body into an IF block, but the trigger still fires, but no PSQL code is executed. So the question is: Is it worth to think about conditional trigger firing or is trigger firing in Firebird that cheap, so we don't have to bother? I think that the Oracle WHEN-clause is simply syntactic sugar for wrapping the entire body in an IF-condition. I don't think there would be much difference performance-wise. If a WHEN clause implies that the trigger gets fired anyway, then right, this wouldn't make a difference. ;-) Regards, Thomas -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/11 11:24, Mark Rotteveel wrote: On Mon, 10 Oct 2011 09:11:40 +0200, Thomas Steinmaurer t...@iblogmanager.com wrote: I think that the Oracle WHEN-clause is simply syntactic sugar for wrapping the entire body in an IF-condition. I don't think there would be much difference performance-wise. If a WHEN clause implies that the trigger gets fired anyway, then right, this wouldn't make a difference. ;-) I don't know enough about the inner-workings of Oracle but something will need to be executed and evaluated to make that WHEN decision, and I would be surprised if it would have any serious difference in performance to do that separately before the trigger or inside the actual trigger-code. Based on that I would think that it is simply syntactic sugar and the actual generated objectcode is simply the equivalent of having an IF inside the trigger code, as it would reduce the number of code execution points (and the work for Oracle in terms of code maintenance for a separate conditional evaluation before triggers). At the first glance it's possible to avoid loading trigger's body with WHEN clause. And in a case when body is true big (and calls a lot of SPs) and WHEN clause is satisfied really rare, we can have some performance effect here. But telling true I do not think that such game is worth playing - too many conditions should be met. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
At the first glance it's possible to avoid loading trigger's body with WHEN clause. Should we check permissions of trigger's code ? If yes - we will load it anyway at query (insert\update\delete) prepare time. Regards, Vlad -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/11 11:47, Dimitry Sibiryakov wrote: 10.10.2011 9:41, Alex Peshkoff wrote: But telling true I do not think that such game is worth playing - too many conditions should be met. It is worth for Oracle because a) condition is evaluated in SQL machine and let it avoid switching to PL/SQL machine if the condition doesn't met. (Such switch is very slow.) b) trigger body can be in Java. Firebird don't have both these problems. Please take into an account - in FB3 trigger body can also be in Java (or speaking generally on any language). And this makes me start to think that WHEN clause may be something useful in that case. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/11 11:47, Dimitry Sibiryakov wrote: 10.10.2011 9:41, Alex Peshkoff wrote: But telling true I do not think that such game is worth playing - too many conditions should be met. It is worth for Oracle because a) condition is evaluated in SQL machine and let it avoid switching to PL/SQL machine if the condition doesn't met. (Such switch is very slow.) b) trigger body can be in Java. Firebird don't have both these problems. Please take into an account - in FB3 trigger body can also be in Java (or speaking generally on any language). And this makes me start to think that WHEN clause may be something useful in that case. When proposing the WHEN clause, I hadn't support for external languages in mind, but if this means, that the trigger don't need to fire up a virtual environment (e.g. JVM) for external language code execution, then this might be performance improvement. But then it depends on what is necessary that the expression in the WHEN clause can be evaluated. Regards, Thomas -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/11 11:53, Vlad Khorsun wrote: At the first glance it's possible to avoid loading trigger's body with WHEN clause. Should we check permissions of trigger's code ? If yes - we will load it anyway at query (insert\update\delete) prepare time. We should not check permissions of trigger in that case. Imagine clause: WHEN CURRENT_USER != 'SYSDBA' If such clause is inside trigger code then it not affects security checks and *whole* trigger code is checked. SQL standard says something about security checks for conditional triggers ? Regards, Vlad -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
10.10.2011 12:26, Vlad Khorsun wrote: If such clause is inside trigger code then it not affects security checks and *whole* trigger code is checked. SQL standard says something about security checks for conditional triggers ? I believe the rules are the same. In general, it's impossible to evaluate the trigger condition at the prepare time (it can refer to the fields of the current record), so I strongly believe we shouldn't even try doing that. So IMO this feature is not going to affect the body parsing and permission checking. Dmitry -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
10.10.2011 12:26, Vlad Khorsun wrote: If such clause is inside trigger code then it not affects security checks and *whole* trigger code is checked. SQL standard says something about security checks for conditional triggers ? I believe the rules are the same. In general, it's impossible to evaluate the trigger condition at the prepare time (it can refer to the fields of the current record), so I strongly believe we shouldn't even try doing that. So IMO this feature is not going to affect the body parsing and permission checking. Exactly. And we still must load all active PSQL triggers despite of any declared condition. Seems conditional triggers have sence only for external (non-PSQL) triggers. Regards, Vlad -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/11 13:48, Vlad Khorsun wrote: 10.10.2011 12:26, Vlad Khorsun wrote: If such clause is inside trigger code then it not affects security checks and *whole* trigger code is checked. SQL standard says something about security checks for conditional triggers ? I believe the rules are the same. In general, it's impossible to evaluate the trigger condition at the prepare time (it can refer to the fields of the current record), so I strongly believe we shouldn't even try doing that. So IMO this feature is not going to affect the body parsing and permission checking. Exactly. And we still must load all active PSQL triggers despite of any declared condition. Seems conditional triggers have sence only for external (non-PSQL) triggers. Yes. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On Mon, 10 Oct 2011 12:48:51 +0300, Vlad Khorsun hv...@users.sourceforge.net wrote: 10.10.2011 12:26, Vlad Khorsun wrote: If such clause is inside trigger code then it not affects security checks and *whole* trigger code is checked. SQL standard says something about security checks for conditional triggers ? I believe the rules are the same. In general, it's impossible to evaluate the trigger condition at the prepare time (it can refer to the fields of the current record), so I strongly believe we shouldn't even try doing that. So IMO this feature is not going to affect the body parsing and permission checking. Exactly. And we still must load all active PSQL triggers despite of any declared condition. Seems conditional triggers have sence only for external (non-PSQL) triggers. If you are going to add it, I'd say: add it for everything, not just non-PSQL triggers, if only for consistency :) Mark -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/2011 06:48, Vlad Khorsun wrote: Seems conditional triggers have sence only for external (non-PSQL) triggers. Moreover, only when the call is cost (like Java, but not for C++). Adriano -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
10.10.2011 12:36, Adriano dos Santos Fernandes wrote: Wrong reason, as Oracle doesn't support external triggers. Oracle SQL Reference for version 10 has other opinion: -- quote --- CREATE TRIGGER salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (new.job_id 'AD_VP') CALL check_sal(:new.job_id, :new.salary, :new.last_name) The procedure check_sal could be implemented in PL/SQL, C, or Java. -- quote end --- -- SY, SD. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
10.10.2011 14:43, Dimitry Sibiryakov wrote: Wrong reason, as Oracle doesn't support external triggers. Oracle SQL Reference for version 10 has other opinion: -- quote --- CREATE TRIGGER salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (new.job_id 'AD_VP') CALL check_sal(:new.job_id, :new.salary, :new.last_name) The procedure check_sal could be implemented in PL/SQL, C, or Java. -- quote end --- Here we have an external procedure and some syntax sugar to call it at the trigger invocation. Still no external triggers, as stated by Adriano. Dmitry -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/11 14:36, Adriano dos Santos Fernandes wrote: On 10/10/2011 04:47, Dimitry Sibiryakov wrote: b) trigger body can be in Java. Firebird don't have both these problems. Wrong reason, as Oracle doesn't support external triggers. You would just need to code a PL/SQL trigger that calls an external procedure/function, so you would be able to use a IF anyway. We should better not forget about conveniences of the end-user. Please agree, that code with WHEN + non-PL/SQL trigger looks better than dumb PL/SQL trigger + external function. -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support conditional trigger firing (WHEN clause ala Oracle)
On 10/10/2011 07:43, Dimitry Sibiryakov wrote: 10.10.2011 12:36, Adriano dos Santos Fernandes wrote: Wrong reason, as Oracle doesn't support external triggers. Oracle SQL Reference for version 10 has other opinion: -- quote --- CREATE TRIGGER salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (new.job_id 'AD_VP') CALL check_sal(:new.job_id, :new.salary, :new.last_name) The procedure check_sal could be implemented in PL/SQL, C, or Java. -- quote end --- You said it, *procedure*. It's fake external trigger. Nothing really different than: FOR EACH ROW BEGIN IF (new.job_id 'AD_VP') THEN check_sal(:new.job_id, :new.salary, :new.last_name); END IF; END; So my point, trigger's WHEN clause is not a special thing for external calls in Oracle. Adriano -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
