Re: recursive or cascade trigger?
Alexander A. Dokumentov (juice@mechanics.msk.su) wrote:
: Hi,
: How can I solve the problem:
: I have to do somthing like this
Your create table looks like this...
CREATE TABLE SELF_REF(
ID INTEGER NOT NULL not default,
REF INTEGER,COLOR CHAR(20));
INSERT INTO SELF_REF VALUES (1,NULL,'RED');
INSERT INTO SELF_REF VALUES (2,1,'RED');
INSERT INTO SELF_REF VALUES (3,1,'RED');
INSERT INTO SELF_REF VALUES (4,2,'RED');
INSERT INTO SELF_REF VALUES (5,2,'RED');
You then create a DbProcedure and a rule as follows:
create procedure mod_col(new_col = char(20), old_ref = integer) as
begin
update self_ref
set color = :new_col
where id = :old_ref;end;
create rule mod_rule
after update of self_ref
where old.ref is not null
execute procedure mod_col(new_col = new.color, old_ref = old.ref);
: Possibly it is not the best programming style, but object-oriented.
: I failed to do this in ORACLE 7 and INFORMIX-OL 6.
Why did this not work in the other two?
: 1 IS IT POSSIBLE IN ANY RDBMS ???
: (ORACLE, INFORMIX, INGRES, MS SQL, SYBASE, INTERBASE, ....???)
At least in one of the above..
: 2 IS EVERETHING ABOVE CORRECT ?
Syntax wise, who knows. If you mean if it works then it is correct then
yes it is...
Jon
--
#include <disclaimer.h>
+---------------------------------------------------------------------+
| Agrodcontraelaphantisticadecabizifridayphobia. The fear of being |
| caught in wide open spaces while held against an elephant on a busy |
| friday the 10th of *any* month. No tijm toe waist... |
+---------------------------------------------------------------------+
| Jon Machtynger(jonm@ingres.com) |
| Bvd de la Woluwe 34 Bte. 13 |
| Brussels. Belgium. Ph: 02-774 49 23 Fax: 02-773 28 09 |
+---------------------------------------------------------------------+