By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,278 Members | 2,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,278 IT Pros & Developers. It's quick & easy.

Creating trigger

P: 3
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.......whats the problem.....
Feb 29 '08 #1
Share this Question
Share on Google+
3 Replies


amitpatel66
Expert 100+
P: 2,367
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.......whats the problem.....
What is the error that it is displaying?
Feb 29 '08 #2

debasisdas
Expert 5K+
P: 8,127
Please find more about triggers here and here .
Mar 3 '08 #3

P: 93
one of the good example to avoid mutation
Expand|Select|Wrap|Line Numbers
  1.  
  2. Create table CUG 
  3.  
  4. drop table CUG cascade constraints;
  5. create table CUG (
  6.   id_cug      number(12) not null primary key,
  7.   id_B        number(12) not null,
  8.   type        number(1),
  9. foreign key (id_B) references CUG (id_cug) 
  10. on delete cascade); 
  11.  
  12. Next we create a temporary table to avoid the "Mutating Table Problem". 
  13.  
  14. drop table CUGTMP;
  15. create global temporary table CUGTMP (
  16.   id_B        number(12),
  17.   type        number(1))
  18. on commit delete rows; 
  19.  
  20. The following trigger checks new rows (Inserts) in CUG 
  21.  
  22. create or replace trigger bi_r
  23. before insert on CUG
  24. for each row
  25. declare
  26.   l_type     CUG.type%type;
  27. begin
  28.   if (:new.type in (3,4)) then
  29.     select type into l_type from CUG
  30.      where id_cug = :new.id_B;
  31.   end if;
  32.   if (l_type != 2) then
  33.      raise_application_error(-20002,
  34.      'C and D CUGs must have a leading B');
  35.   end if;
  36. end;
  37.  
  38. The following Trigger saves the new values for id_B in the temporary table. 
  39.  
  40. create or replace trigger au_r
  41. after update of id_B on CUG
  42. for each row
  43. begin
  44.   insert into CUGTMP (id_B,type)
  45.   values (:new.id_B,:new.type);
  46. end;
  47.  
  48. The following Trigger finally checks, that C and D CUGs belong to a B CUG. 
  49.  
  50. create or replace trigger au_s
  51. after update of id_B on CUG
  52. declare
  53.   l_id_B        number(12);
  54.   l_typeCD      number(1);
  55.   l_typeB       number(1);
  56.   cursor cur_cugtmp is
  57.   select id_B,type
  58.    from CUGTMP;
  59. begin
  60.   open cur_cugtmp;
  61.   loop
  62.     fetch cur_cugtmp into l_id_B,l_typeCD;
  63.     exit when cur_cugtmp%notfound;
  64.      select type into l_typeB from CUG
  65.       where id_cug = l_id_B;
  66.     if (l_typeB != 2) then
  67.        raise_application_error(-20002,
  68.        'C and D CUGs must have a leading B');
  69.     end if;
  70.   end loop;
  71.   close cur_cugtmp;
  72. end;
  73. //
  74.  
now Test insert and update
Mar 4 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.