Creating trigger

[pravatjena]

Hi

Plz solve my problem.......

My problem is i have created three users suppose
user1
user2
user3

i created a table in user1 then i created a table in user3 same as the table in user1 but name is differnt and i created a triger in user2. then i want the trigger should insert the inserted or updated or deleted records in user1 table to user3 table...also i have given all the required previllages still the trigger cant do the required task.......what s the problem.....

[amitpatel66]

Hi

Plz solve my problem.......

My problem is i have created three users suppose
user1
user2
user3

i created a table in user1 then i created a table in user3 same as the table in user1 but name is differnt and i created a triger in user2. then i want the trigger should insert the inserted or updated or deleted records in user1 table to user3 table...also i have given all the required previllages still the trigger cant do the required task.......what s the problem.....

What is the error that it is displaying?

[debasisdas]

Please find more about triggers here and here .

[subashsavji]

one of the good example to avoid mutation
[code=oracle]

Create table CUG

drop table CUG cascade constraints;
create table CUG (
id_cug number(12) not null primary key,
id_B number(12) not null,
type number(1),
foreign key (id_B) references CUG (id_cug)
on delete cascade);

Next we create a temporary table to avoid the "Mutating Table Problem".

drop table CUGTMP;
create global temporary table CUGTMP (
id_B number(12),
type number(1))
on commit delete rows;

The following trigger checks new rows (Inserts) in CUG

create or replace trigger bi_r
before insert on CUG
for each row
declare
l_type CUG.type%type;
begin
if (:new.type in (3,4)) then
select type into l_type from CUG
where id_cug = :new.id_B;
end if;
if (l_type != 2) then
raise_applicati on_error(-20002,
'C and D CUGs must have a leading B');
end if;
end;
/

The following Trigger saves the new values for id_B in the temporary table.

create or replace trigger au_r
after update of id_B on CUG
for each row
begin
insert into CUGTMP (id_B,type)
values (:new.id_B,:new .type);
end;
/

The following Trigger finally checks, that C and D CUGs belong to a B CUG.

create or replace trigger au_s
after update of id_B on CUG
declare
l_id_B number(12);
l_typeCD number(1);
l_typeB number(1);
cursor cur_cugtmp is
select id_B,type
from CUGTMP;
begin
open cur_cugtmp;
loop
fetch cur_cugtmp into l_id_B,l_typeCD ;
exit when cur_cugtmp%notf ound;
select type into l_typeB from CUG
where id_cug = l_id_B;
if (l_typeB != 2) then
raise_applicati on_error(-20002,
'C and D CUGs must have a leading B');
end if;
end loop;
close cur_cugtmp;
end;
//
[/code]
now Test insert and update