Wednesday, September 13, 2006

Just, Why?

I'm currently debugging a procedure of the form illustrated below, lovingly recreated using the ubiquitous scott.emp table. I'm also taking suggestions on what might've been going through the head of the person who wrote it.

RENAME emp TO emp_table
/
CREATE OR REPLACE VIEW emp
AS
   SELECT *
     FROM emp_table
/
CREATE OR REPLACE TRIGGER mod_emp
   INSTEAD OF UPDATE
   ON emp
   FOR EACH ROW
DECLARE
   new_emp                       emp_table%ROWTYPE;
   old_emp                       emp_table%ROWTYPE;
BEGIN
   new_emp.empno       := :NEW.empno;
   new_emp.ename       := :NEW.ename;
   new_emp.job         := :NEW.job;
   new_emp.mgr         := :NEW.mgr;
   new_emp.hiredate    := :NEW.hiredate;
   new_emp.sal         := :NEW.sal;
   new_emp.comm        := :NEW.comm;
   new_emp.deptno      := :NEW.deptno;
   old_emp.empno       := :OLD.empno;
   old_emp.ename       := :OLD.ename;
   old_emp.job         := :OLD.job;
   old_emp.mgr         := :OLD.mgr;
   old_emp.hiredate    := :OLD.hiredate;
   old_emp.sal         := :OLD.sal;
   old_emp.comm        := :OLD.comm;
   old_emp.deptno      := :OLD.deptno;
   update_emp (old_emp, new_emp);
END;
/

CREATE OR REPLACE PROCEDURE update_emp (
   old_emp                             emp_table%ROWTYPE
  ,new_emp                             emp_table%ROWTYPE
)
IS
BEGIN
   IF old_emp.empno != new_emp.empno
   THEN
      RETURN;   --can't update primary key attribute
   END IF;

   IF     old_emp.empno IS NOT NULL
      AND new_emp.empno IS NULL
   THEN
      DELETE FROM emp_table
            WHERE empno = old_emp.empno;

      RETURN;
   END IF;

   IF    (    old_emp.ename IS NULL
          AND new_emp.ename IS NOT NULL)
      OR (    old_emp.ename IS NOT NULL
          AND new_emp.ename != old_emp.ename)
   THEN
      UPDATE emp_table
         SET ename = new_emp.ename
       WHERE empno = old_emp.empno;
   END IF;
   -- ..
   -- ..
   -- ..
   IF    (    old_emp.deptno IS NULL
          AND new_emp.deptno IS NOT NULL)
      OR (    old_emp.deptno IS NOT NULL
          AND new_emp.deptno != old_emp.deptno)
   THEN
      UPDATE emp_table
         SET deptno = new_emp.deptno
       WHERE empno = old_emp.empno;
   END IF;
END;
/

11 comments:

Anonymous said...

That's easy. They were told to redesign the database schema without changing existing client code.

It looks like they only got as far as putting a proxy system in place without actually changing the database tables.

Rab Boyce said...

everbody knows that this sort of thing shouldn't happen. that's why we have ORM tools like hibernate. ;P

Anonymous said...

What was going through the programmers mind? Who knows, but maybe they should lay off the drugs!

Nigel said...

Looks to me like this was an attempt at "optimization" - only updating the columns that have actually been changed. Just a shame that if you change 20 columns, you get 20 separate updates... so more like "pessimization" really.

Rab Boyce said...

personally, im still on the fence on the whole ORM thing. part of me screams that its just a bad idea. but on the other hand, I can see it being very useful in some simple cases, but then, if the cases are simple what's wrong with just hand coding some data access mechanisms....

Anonymous said...

"...emerging some time later to announce that he has fixed a 'problem' with the Hibernate mappings."

...And isn't the "fix" normally to replace the rows and columns in the tables with Bee-Lobs that the application "manages" itself?

Niall said...

I might be missing something but my guess would be that this code predates "rename table" and has never been changed.

William Robertson said...

From the days when you could create an INSTEAD-OF trigger but not rename a table, you mean?

Rab Boyce said...

What's wrong with storing all your data in xml in Bee-Lobs anyway? If they are in an Oracle table, then surely that's relational enough for most people!

Jeff Hunter said...

At lunch, they were thinking:
"One more beer won't hurt, will it?"

Anonymous said...

That's something my *project manager* could have written, and checked into the source repository. Without telling the developers of his changes, of course (nor testing it, btw)