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; /
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.
Subscribe to:
Post Comments (Atom)
11 comments:
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.
everbody knows that this sort of thing shouldn't happen. that's why we have ORM tools like hibernate. ;P
What was going through the programmers mind? Who knows, but maybe they should lay off the drugs!
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.
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....
"...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?
I might be missing something but my guess would be that this code predates "rename table" and has never been changed.
From the days when you could create an INSTEAD-OF trigger but not rename a table, you mean?
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!
At lunch, they were thinking:
"One more beer won't hurt, will it?"
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)
Post a Comment