Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

For update cascade

542647Jan 11 2007 — edited Jan 12 2007
Hi, i am migrating from SQL server to oracle and looking for alternative of ON UpdATE CASCADE IN SQL server.
I have used after update trigger but facing problem
Please find below the code which i am using

create table t1(c1 number primary key);

create table t2(c1 number primary key, c2 number);

alter table t2 add constraint FK_t1c1
foreign key (c2) references t1(c1)
Deferrable initially deferred;

SQL>> insert into t1 values(1);
1 row created.

SQL>> insert into t1 values(2);
1 row created.

SQL>> insert into t2 values(100, 1);
1 row created.

SQL>> insert into t2 values(101, 2);
1 row created.

create or replace trigger trFKCU_t1
after update of c1 on t1
for each row

begin
dbms_output.put_line( 'setting ' || :old.c1 || ' to ' || :new.c1 );


update t2
set c2 = :new.c1
where c2 = :old.c1;
dbms_output.put_line( 'setting ' || :old.c1 || ' to ' || :new.c1 );
end;
/
Trigger created.


Now when i am updating like
Update t1 set c1=c1+1;
It gives me 3 for both rows in c2 column of t2 table instead of giving 2 and 3.
Can anyone help in putting for loop or some other solution.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 9 2007
Added on Jan 11 2007
9 comments
507 views