Trigger Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Trigger Problem

  1. #1
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220

    Trigger Problem

    I got a schema called Gary and another called SS
    I m creating this trigger by loggin on to SS on Gary.G_Diary

    what privileges do i need to have if any required.

    If an insert is performed on Gary.G_DIARY i m inserting into my local table. I want to acheive this...but i m getting problems with :new and ld variables.

    :new.gary.g_diary.columnname (can this be given)

    Below is the code, Could anyone pl let me know whas wrong here.

    Create or Replace Trigger Gdiary_ict
    After Insert or Update or Delete on GARY.G_DIARY
    for each row
    declare
    v_msg varchar2(100);
    all_day varchar2(1);
    recs number(8);
    Begin
    if inserting then
    insert into G_DIARY_local (event_id,room_no,event,event_date,all_day,start_hour,start_mins)
    values(ghall_eventdiary.nextval,:NEW.G_DIARY.ROOM_NO,:NEW.G_DIARY.EVENT,
    :NEW.g_diary.event_date,'Y',:NEW.G_DIARY.HOUR_VALUE,:NEW.G_DIARY.HOUR_MINS);
    end if;
    end;

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you have "CREATE ANY TRIGGER" priv?

    Tamil

  3. #3
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi,

    I want to create trigger in my schema.

    SS

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Let Gary should create public synonym on his table.

    Then SS can reference the refer table w/o qualifier in the trigger.
    I think it will work. I will also do my test.

    Tamil

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    OK. I tested and it worked.

    SS should have "CREATE ANY TRIGGER" priv, because SS is creating a trigger on a table that he has not owned.

    You do not need use :NEW.tablename.columnname.
    Just use :NEW.COLUMNAME.


    Tamil
    Last edited by tamilselvan; 09-30-2003 at 01:38 PM.

  6. #6
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Thanks Tamil for the info.

    got it working

    SS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width