If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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.