DISQUS

Thanx for the SELECT * IS EVIL link. It's not bad, and i can can finally point people to something when i make the same comment.

I personally prefer Oracle's was because it’s easy to read, and i personally find the ANSI syntax to be very hard to follow especially once it starts to nest.

"It was not an easy task, believe me. If I had multiple Eddie Awad clones, I would have been much happier."

Or at least *one* of you would have.

So, TRIM and TO_CHAR seem the same by a DATE:

SELECT TRIM(SysDate), DUMP(TRIM(SysDate)) FROM Dual UNION ALL
SELECT TO_CHAR(SysDate), DUMP(TO_CHAR(SysDate)) FROM Dual;

05-SEP-08 Typ=1 Len=9: 48,53,45,83,69,80,45,48,56
05-SEP-08 Typ=1 Len=9: 48,53,45,83,69,80,45,48,56

TRUNC, however, leaves it as a DATE, which is possible the best way to remove the time.

SELECT SysDate, DUMP(SysDate) FROM Dual UNION ALL
SELECT TRUNC(SysDate), DUMP(TRUNC(SysDate)) FROM Dual;

05-SEP-08 Typ=13 Len=8: 216,7,9,5,8,51,44,0
05-SEP-08 Typ=13 Len=8: 216,7,9,5,0,0,0,0

@Eddie

I understand your point. Personally, i don't like to force the developers to do one way or another, unless it is a question of breaking data integrity.

IMHO, developers make up their own numbers because it is not done for them. However, if a TRIGGER automagically pops the SEQUENCE for them, they won't bother, unless they need to test a specific case.

Also, there may be the situation where a TRIGGER is preffered, but now and then needs to be overrided because a query will not allow the use of a RETURNING clause. In which case, the SEQUENCE needs to be popped manually and the TRIGGER overridden.

As for the IF, i thought WHEN is faster as it is evaulated before the TRIGGER is executed.

@John

I agree with Eddie on most TRIGGERs. It is a real pain trying to figure out who fired what and when.

An then when people *rely* on the TRIGGERs, but some TRIGGERs have not been updated with the new rules, and so on, figuring out the logic (which may include firing order) is more trouble than its worth.

If you want to control what developers can do to the DB, remove access to the TABLEs, and use PROCEDUREs instead to implement the approved methods. Using TRIGGERs is more of a way to cleanup, not prevent.

"In general, I do not like database triggers, they hide your logic and make it difficult to debug. But, in this case, I believe that using a database trigger to populate a primary key with a sequence value - or any unique value for that matter - is the right thing to do."

Eddie, i agree with you here, perfectly!

My "excuse" for allowing TRIGGERs is:

I like TRIGGERs for DB maintenance. For example, if a history is maintained, a change-log, or the like, items that are in a sense part of the DB model, i believe the DB should do it. Hmm.. it isn't so cut and dry, i guess i need to work on a more specific rule.

Either way, in this case, the PK has no intrinsic value, and is just being used to have a good way to uniquely refer to the record.

I have made a recent change in my policy, however, to allow overrides. In the TRIGGER i started to as WHEN (Old.Id IS NULL). This way, the TRIGGER is helpful, but doesn't overwrite what the developer wants to do.

@Dan

Heh. It is a way of saying i don't like change. Not that i don't like it. :)

Definitely different. I didn't recognize it!

"So, in general, as a best practice, always use named notation when invoking PL/SQL procedures and functions. To avoid breaking existing code, add new parameters with DEFAULT values to the end of the parameter list of existing procedures or functions."

Wouldn't overloading the routine accomplish the same goal?

@Dan OK, then try it *just before* it is frozen. :)

"(refrigeration optional when desperate)."

Unless were talking about Coca Cola. Without refrigeration, it's deadly. The colder, the better, unless it is frozen. The best case for Coke? That's when it is liquid, but the fizz freezes when it rises.

My first browser was either the AOL browser or GNN Navigator.

I just tried in 10g XE

INSERTing REVERSE(Date) has interesting results. UPDATing, however, seems to choose a valid DATE.