Fun With Triggers

I don't care a whole lot for triggers. About the only thing I see of use is for some sort of auditing.

That said, I don't know a whole lot about them either. I don't know the specifics of how they work. I was tested today...

CREATE TABLE t
(
  update_date DATE DEFAULT SYSDATE,
  update_user VARCHAR2(30)
);

INSERT INTO t ( update_date ) 
VALUES ( ADD_MONTHS( SYSDATE, 100 ) );

INSERT INTO t ( update_date ) 
VALUES ( ADD_MONTHS( SYSDATE, -22 ) );

CJUSTICE@XE>SELECT * FROM T;

UPDATE_DA UPDATE_USER
--------- ------------------------------
31-OCT-16
31-AUG-06

Easy enough.

My requirement is that I capture the update_user, specifically in the case where it is not supplied. And this is where I ran into a wall.

CREATE OR REPLACE
TRIGGER biu_t
  BEFORE INSERT OR UPDATE
  ON t
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
BEGIN
  IF :NEW.update_user IS NULL THEN
    :NEW.update_user := USER;
  END IF;
END;
/
show errors

Easy enough.

UPDATE t 
  SET update_date = ADD_MONTHS( SYSDATE, -12 );

CJUSTICE@XE>SELECT * FROM T;

UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-07 CJUSTICE
30-JUN-07 CJUSTICE

2 rows selected.

OK, not too bad. Let's try specifying the UPDATE_USER.

UPDATE t
  SET update_date = ADD_MONTHS( SYSDATE, 12 ),
      update_user = 'BOLLOCKS';

UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-09 BOLLOCKS
30-JUN-09 BOLLOCKS

Good, everything's working as expected...or is it? Let's connect as another user:

CJUSTICE@XE>conn hr/testing@xe
Connected.

UPDATE cjustice.t SET update_date = SYSDATE - 100;

HR@XE>SELECT * FROM cjustice.t;

UPDATE_DA UPDATE_USER
--------- ------------------------------
22-MAR-08 BOLLOCKS
22-MAR-08 BOLLOCKS

What? Why didn't the user get updated with HR? Let's add some dbms_output statements to the trigger:

CREATE OR REPLACE
TRIGGER biu_t
  BEFORE INSERT OR UPDATE
  ON t
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
BEGIN
  dbms_output.put_line( 'New Value:  ' || :NEW.update_user );
  dbms_output.put_line( 'Old Value:  ' || :OLD.update_user );

  IF :NEW.update_user IS NULL THEN
    :NEW.update_user := USER;
  END IF;
END;
/
show errors

HR@XE>UPDATE cjustice.t SET update_date = SYSDATE + 100;
New Value:  BOLLOCKS
Old Value:  BOLLOCKS
New Value:  BOLLOCKS
Old Value:  BOLLOCKS

2 rows updated.

Elapsed: 00:00:00.04
HR@XE>SELECT * FROM cjustice.t;

UPDATE_DA UPDATE_USER
--------- ------------------------------
08-OCT-08 BOLLOCKS
08-OCT-08 BOLLOCKS

2 rows selected.

So UPDATE_USER was not updated with the value of HR, which I would expect. The :NEW and :OLD values are exactly the same...I'm guessing that Oracle makes a copy of the record first (puts it into a collection or something?) so UPDATE_USER would never be NULL.

Ultimately, I scrapped the update of UPDATE_USER in the trigger. I did force the UPDATE_DATE = SYSDATE, and that was it.

Ideally, I would specify that in the package call, but we aren't there yet. In my opinion, no other user would have UPDATE, INSERT or DELETE privileges on the table. But that's what I have.

Can someone with more experience with triggers help me out? Or advise me on what's going on?

Hertz Still Sucks

From Hertz:

Dear Mr. Justice:

We have received a report from USAA regarding your rental in Baltimore. We appreciate the opportunity to address your concerns.

We are always concerned when a customer is disappointed with our service for any reason. When customers accept the Fuel Purchase Option (FPO), they are pre-paying for a full tank of fuel in advance at a price per gallon that is comparable to local gasoline stations. There is no refund given for any fuel left in the tank at return, and this was stated on the rental record you signed; it is noted on the second page of the contract at the top. In addition, the estimated charges at the time of rental were $117.01, and this is the exact amount you were charged. However, we sincerely apologize for any misunderstanding concerning these charges and regret the confusion regarding the FPO.

At the same time, we realize communication problems can occasionally occur. In the interest of customer satisfaction, we have processed a $30.00 credit to your MasterCard account. This is approximately one-half of the FPO charge.

Mr. Justice, we appreciate your business. We hope to serve you again soon with more positive results.

Sincerely,

Redacted
Executive Administrator
OKC Customer Relations
The Hertz Corporation
P.O. Box 26120
14501 Hertz Quail Springs Parkway
Oklahoma City, OK 73134
U.S.A.

No, they didn't call either. Nor did they honestly address any of my complaints.

I never denied that I signed the agreement (thereby responsible for the FPO) and I didn't expect any compensation for it. My problem was with the particular agent who knew I would be in town for one day only and sold it to me anyway without telling me the specifics.

Oh well. Avis I'd use since they called my father and addressed his concerns. Hertz, never again.

Corporate Life III

See Part I and Part II.

In Part I of Corporate Life, I said:

It feels like I've experienced about every event I could have imagined:
1. In January of 2007, a new CIO/SVP was hired and promptly restructured (replaced the VPs) the IT department.
2. In October of 2007, we had a nifty FBI raid.
3. January of 2008, we appear to be losing our CEO, CFO and General Counsel.

prodlife then mentioned that I hadn't experienced layoffs.

Four months later I did, on May 22, WellCare laid off 208 employees. I blogged it. I was fired.

I'm up to five corporate experiences.

I'm still missing the merger/acquisition and the IPO. Anything I'm missing?

Design - The Entity

I love designing databases. Specifically, data modeling. I love trying to figure out how the data fits together. Is this an attribute of that? Is it shared with something else? What's the relationship? One to many? One to one? Many to many? Hierarchical?

Let's start with people and addresses. A fairly easy mapping. One person can have one or more addresses, or one to many. The basics of that are drawn out below:

The primary key (whether natural or surrogate, defined here as a surrogate) for PEOPLE is stored as a Foreign Key in the ADDRESSES table. To guarantee that a PEOPLE key is supplied for each address, you should add a NOT NULL constraint along with the FK constraint.

Not too difficult.

What if down the road you need to add organizations? Do you shove them into the people table or do you create a new table for them? My initial thought is to create a new table called ORGANIZATIONS.

Easy enough right?

Now I want to track addresses for the ORGANIZATION table. Hmmm...how to do that? My first thought was to just create another address table.

Hmmm...now I have addresses in two different tables. I've introduced the chance of having non-standardized addresses (two entry points, or maybe I don't have the same attributes in both tables). Writing a query to get all the addresses (for a mailing perhaps) will be a bit more difficult.

How about just adding another column to the original ADDRESS table?

That works...sort of. I must remove the NOT NULL constraint for PEOPLE and I definitely can't add one for ORGANIZATION. But they're in one table and I can have one entry point (it would have to be conditional though). What if in the future I add another table? Do I just add another column (FKd) to ADDRESS.

It starts to become a bit unwieldy.

So, enter the ENTITY.

The ENTITY just becomes a placeholder, providing keys for PEOPLE (one to one) and ORGANIZATIONS (one to one). ENTITYID is stored in the ADDRESS table and voila! Perhaps this is called a supertype (not real sure on my terminology here). You now have one entry to the ADDRESS table (well...hopefully). To pull all addresses is easy.

You can use the surrogate key from ENTITY as your primary key in PEOPLE and ORGANIZATIONS, so if you want to bypass the ENTITY table, you can do so easily.

That's my solution anyway. How about you? Same, different? Am I crazy (I'm starting to think so...)?