Thread: SELECT Rules or stored procedure
I have a query that takes two tables (join) and does something on it. Lets say these tables are A and B. What I need is that everytime one of the tables (A) has its rows selected, I want to update the count (which is a column in A) for that row. I am not sure what is the best way to do this. 1)I could create a dummy view and do a RULE on that (I am not sure how to get access to the row's that have been selected). 2) I could create a storedprocedure. Get all the rows to be returned and then, loop through the rows and update the column and then return it. Q1) Which way is better? q2) How does one get access to the rows just selected in the CREATE RULE computation? thanks. -assad
On Tue, Jan 17, 2006 at 09:55:42PM -0700, Assad Jarrahian wrote: > I have a query that takes two tables (join) and does something on it. > Lets say these tables are A and B. > What I need is that everytime one of the tables (A) has its rows > selected, I want to update the count (which is a column in A) for that > row. > > I am not sure what is the best way to do this. > 1)I could create a dummy view and do a RULE on that (I am not sure how > to get access to the row's that have been selected). > 2) I could create a storedprocedure. Get all the rows to be returned > and then, loop through the rows and update the column and then return > it. > > Q1) Which way is better? > q2) How does one get access to the rows just selected in the CREATE > RULE computation? Via NEW and OLD. Read Chapter 34 of the documentation. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On 2006-01-19, "Jim C. Nasby" <jnasby@pervasive.com> wrote: > On Tue, Jan 17, 2006 at 09:55:42PM -0700, Assad Jarrahian wrote: >> q2) How does one get access to the rows just selected in the CREATE >> RULE computation? > > Via NEW and OLD. Read Chapter 34 of the documentation. He's talking about a SELECT rule. A SELECT rule is no more and no less than a view - you can do nothing with select rules that you can't do with a normal view. (You can only have one SELECT rule, it must be named _RETURN, and it must be a DO INSTEAD SELECT rule. If you put such a rule on a plain table, you will find that the table changes into a view.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services