Oracle trigger: capturing one less column of a table

If you want to fire a trigger when any of columns but one is updated, the solution ca be to put all the wanted columns in the script, but there is an easier way:

CREATE OR REPLACE TRIGGER ....... before UPDATE or delete ON
 .......... REFERENCING old AS old new as new FOR EACH ROW
declare
...............................
BEGIN
...........................
if updating ('unwanted_column') then  
         null;
             else
    ........the action of trigger..........;
end if;
exception
WHEN exc THEN
............
    rollback;
END;
/



Be aware that if you use 

if not updating ('unwanted_column') then
   ........the action of trigger..........;
end if;

you'll notice, when you update two or more columns, and the unwanted column is not included, anyway, the condition will not be satisfied and the action will not be done. Only in the case of updating one column will succeed, which is not a solid solution.
So, use the condition somehow that the action will be on the ELSE   branch.
Enjoy!

ORA-00054: resource busy and acquire with NOWAIT specified

First of all, i must say this message maybe is not really scary, because you might think with some patience the problem can be overridden. Well, there is a possible scenario giving some headaches to a DBA:

alter table  tn disable constraint tnc;

Everything is fine 'till now, you have disabled a constraint in order to update some data.

After all these updates, and after you verify the data is compliant with the bussiness rules enforced by the constraint tnc:

alter table  tn enable constraint tnc;

 ORA-00054: resource busy and acquire with NOWAIT specified

Now you are in serious trouble. Working hours mean lots of data inserted in your tables, not anymore protected by that constraint, and the users can broke the bussiness rule from now.

Of course, you may say the same problem can occur during that few seconds while you ran the two DDL commands and the DML commands which modified th table tn. Agree, was not a good practice way of doing things, but this kind of manipulating data can be seen very often in practice, when a DBA or a developer doesn't care or can't wait for a maintenance window, in order to manipulate data, ignoring the possibility of any other session, locking a table.

Unfortunately, there isn't any complete solution for this problem. You can run the following code (for Oracle <10g), which tries for n minutes to achieve the lock on the table and run the DDL command:

DECLARE

  is_ok  BOOLEAN;

  t_l    EXCEPTION;

  PRAGMA EXCEPTION_INIT(t_l, -00054);

  start_time     DATE; -- the moment of the first cycle

  no_min number; --how many minutes the program will run, trying to

                 -- achieve the lock

BEGIN

  is_ok := FALSE;

  SELECT SYSDATE INTO start_time FROM DUAL;

  WHILE (NOT is_ok) AND (SYSDATE < start_time + no_min/24/60)

  LOOP

    BEGIN

      EXECUTE IMMEDIATE ('alter table  tn enable constraint tnc');

        is_ok := TRUE;

      DBMS_OUTPUT.PUT_LINE('Succes!');

    EXCEPTION

    WHEN t_l THEN

      NULL;

      DBMS_LOCK.SLEEP(0.1);

    END;

  END LOOP;

  IF is_ok = FALSE THEN

    DBMS_OUTPUT.PUT_LINE('the constraint is still disabled!');

  END IF;

END;

/

For Oracle versions >10g, there is a parameter, ddl_lock_timeout , doing the same job like the code from above. Any DDL command issued against a table will try for a number of seconds equal to ddl_lock_timeout.

And there is another solution, involving   the QUIESCE concept, but i don't like it and i don't recommend it to anyone.

Good luck!