 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / July 2008[Info-Ingres] BEFORE Rules and Data Change statements
Hi All, Why aren't BEFORE rules allowed to do inserts, updates or deletes? Is there a good reason? Martin Bowes > Why aren't BEFORE rules allowed to do inserts, updates or deletes? > > Is there a good reason? I've not used a BEFORE rule yet so I have to admit I've not heard of this. The only limitation I am aware of is that they have to be row-level rules. It's obvious the DBP can't act on a row that doesn't yet exist. But are you saying it can't do these things to any other row in any other table just because it was executed by a BEFORE rule?  SignatureRoy UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. Martin is correct if a BEFORE RULE is CREATED that executes a DATABSE PROCEDURE that contains an INSERT, UPDATE OR DELETE, the following message is generated when the CREATE RULE statement is executed: E_US18B5 CREATE RULE: Data change statements are not permitted in Procedures triggered by BEFORE rules. Being of devious mind, I created a second PROCEDURE which contains my DML that is called from the first, and low and behold the RULE creates. When the rule is fired, there are no run time errors. BUT I have not checked for side effects or other nastiness. John -----Original Message----- From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Roy Hann Sent: 29 July 2008 16:07 To: info-ingres@kettleriverconsulting.com Subject: Re: [Info-Ingres] BEFORE Rules and Data Change statements Martin Bowes wrote: > Why aren't BEFORE rules allowed to do inserts, updates or deletes? > > Is there a good reason? I've not used a BEFORE rule yet so I have to admit I've not heard of this. The only limitation I am aware of is that they have to be row-level rules. It's obvious the DBP can't act on a row that doesn't yet exist. But are you saying it can't do these things to any other row in any other table just because it was executed by a BEFORE rule? SignatureRoy UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres > Martin is correct if a BEFORE RULE is CREATED that executes a DATABSE > PROCEDURE that contains an INSERT, UPDATE OR DELETE, the following [quoted text clipped - 8 lines] > > BUT I have not checked for side effects or other nastiness. I hope I would do the same thing you did. :-) But even now that Marty's alerted me to the limitation I can't think of a good reason for it. It is not ever a physical error to attempt to update a non-existent row, so that couldn't be the reason. Someone out there knows. SignatureRoy UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. I have looked at the original Ingres paper work that relates to BEFORE triggers and there is nothing that I can see that says that you cannot execute INSERT, UPDATE or DELETE statements from a BEFORE trigger, there was reference to the SQL standard, I have trawled through the relevant parts and have found the following statement: If the trigger is a BEFORE trigger and if, before the completion of the execution of any <SQL procedure statement> simply contained in triggered SQL statements, an attempt is made to execute an SQL-data change statement or an SQL-invoked routine that possibly modifies SQL-data, then an exception condition is raised: prohibited statement encountered during trigger execution. John -----Original Message----- From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Roy Hann Sent: 29 July 2008 17:46 To: info-ingres@kettleriverconsulting.com Subject: Re: [Info-Ingres] BEFORE Rules and Data Change statements John Smedley wrote: > Martin is correct if a BEFORE RULE is CREATED that executes a DATABSE > PROCEDURE that contains an INSERT, UPDATE OR DELETE, the following > message is generated when the CREATE RULE statement is executed: > E_US18B5 CREATE RULE: Data change statements are not permitted in > Procedures triggered by BEFORE rules. > Being of devious mind, I created a second PROCEDURE which contains my > DML that is called from the first, and low and behold the RULE creates. > When the rule is fired, there are no run time errors. > BUT I have not checked for side effects or other nastiness. I hope I would do the same thing you did. :-) But even now that Marty's alerted me to the limitation I can't think of a good reason for it. It is not ever a physical error to attempt to update a non-existent row, so that couldn't be the reason. Someone out there knows. SignatureRoy UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.