473,320 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Creating trigger

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
3 1450
amitpatel66
2,367 Expert 2GB
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
8,127 Expert 4TB
Please find more about triggers here and here .
Mar 3 '08 #3
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

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

Similar topics

2
by: moklet | last post by:
i've been trying to create an insert/update trigger on v_$session but with no success. following is my code: 1 create or replace trigger trg_module 2 instead of insert or update on t_$session...
7
by: Justin | last post by:
I am extremely new at SQL Server2000 and t-sql and I'm looking to create a simple trigger. For explanation sake, let's say I have 3 columns in one table ... Col_1, Col_2 and Col_3. The data type...
1
by: efinney | last post by:
Hi, I'm a newbie to sql server and this may be a really dumb question for some you. I'm trying to find some examples of sql server triggers that will set columns (e.g. the created and modified...
24
by: jonathon | last post by:
Hi all, I have a web app with a popup window for entering data. I don't want to access the web every time this window is opened, as most of the app is AJAX. But I can't figure out how to open...
3
by: gimme_this_gimme_that | last post by:
Hi, Start up db2 8.2 as "db2 -td@" and execute the following command : CREATE OR REPLACE TRIGGER "BZ".ALERT_CREATED_DATE BEFORE INSERT ON TS_ALERTS REFERENCING OLD AS OLD NEW AS NEW FOR...
5
by: Peter Erickson | last post by:
I am running postgresql 7.4.2 and having problems creating a trigger function properly. I keep getting the following error: ERROR: OLD used in query that is not in rule I have a table called...
2
by: Scott Cain | last post by:
Hello, I am trying to create audit tables for all of the tables in my database. The function, table and trigger create statements are below. Apparently, I am not doing it quite right, because I...
6
by: | last post by:
Here's the scenario. This is going to live in SharePoint, but it's an ASP/HTML issue I think. I need to be able to host an existing portlet in a sharepoint window. The trick is that I need to...
3
by: ramesh1210 | last post by:
please help me, I had created a table, CREATE TABLE THETABLE ( id_num int IDENTITY(1,1), DT datetime, NM varchar(30) )
2
by: popprem | last post by:
Hi, I have to feed database A from database B which are in different machines but in the same network when a insert/update occurs on database B. I decided to use trigger for this and was trying...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.