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?
Triggers are disabled on the table, on which sqlloader operates.
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!
maybe i need to re-phrase
in my .ctl file i have:
replace into table mm_order_detail
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?
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.
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.