DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: trigger question

  1. #1
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    I am having trouble with a trigger. I have a trigger that is based on an update from sqlldr. The only time the trigger works is when I drop it, then add it right before I run sqlldr. However if I don't drop it again, and try to run sqlldr, then it won't show the updates. Any ideas?

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    Triggers are disabled on the table, on which sqlloader operates.

  3. #3
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    Any Idea how I fix that?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Triggers are not disabled during SQL*Loader loading (unless you load with the direct path option)!

    But you say you have *UPDATE* trigger. SQL*loader performs only *INSERTS*, it can't update existing rows, so your trigger can't fire while loading new records with SQL*Loader!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    maybe i need to re-phrase

    in my .ctl file i have:

    load data
    replace into table mm_order_detail
    etc.

    and in the trigger i have on update or insert. and it does update the tables. however if i am logged into sqlplus and i run sqlldr from another shell, then i don't see the update. i have to log out of sqlplus, run sqlldr, then log back into sqlplus to see that the trigger worked. does this clarify?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I repeat: SQL*Loader itself can never *update an existing row in a table! It performs only inserts, not updates. Period.

    LOAD DATA REPLACE in a .ctl only means that before it starts loading, if there are any rows in that table it will first delete those rows and then insert the new ones from the dataq file.

    So if you have ON UPDATE trigger on that table, that trigger will never fire during load. If you have ON UPDATE OR INSERT trigger, then the trigger will fire because of the inserts, updates still never ocurs during SQL*load.

    Maybe you should post the code of your trigger. But i still don't understand what do you mean by connecting/disconnecting, now_you_see_it_now_you_dont and what do you mean by "I see/don't see the updates" (again, there can be no updates!!!!!).

    Need more exact and detailed description of the problem.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    sorry for my lack of specifics. i took your note and removed the on update part from the trigger, now it is just on insert. what i mean by the "connect/disconnect" bussiness is that if is this:

    say i have table x, and i have a trigger runs a stored procedure when table y on insert occurs. the stored procedure does not affect table y, just table x. if i am logged into sqlplus in one shell, and have another shell opened (just in unix) and i run sqlldr for table y, i can select * from table x see the changes that the procedure has done. however if i update table x, then run sqlldr again i do not see the changes. if i log out of sqlplus then run sqlldr, then log back into sqlplus i see the changes.

    i imagine that this is quite confusing, so if it is a hedache to read then just ignore. however if you understand what i am asking, any help is appriciated.

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