Re: Help: How to do the UPDATE Trigger?

I agree with the last comment that the table is denormalized (i.e Breaks first normal form), but this is a physical table not a logical table so it may still be a good descision.

Oracle 8 offers arrays. This would enable you to do this logic in a loop. I would give examples but have never done it myself.

Violin wrote:

> Hello all,
> I have 2 tables: DAILY and MONTHS
>
> DAILY: Example of table DAILY:
> ------------------------------- -------------------------------------
> ON_DATE CHAR(8) 19990301 A
> TYPE CHAR(1) 19990302 A
> 19990303 B
> :::::::::::::::
> 19990331 D
>
> MONTHS: Example of table MONTHS:
> ------------------------------ ----------------------------------------
> ON_YYMM CHAR(6) 199903 A A B ::::::::::::::::: D
> TYPE_01 CHAR(1)
> TYPE_02 CHAR(1)
> TYPE_03 CHAR(1)
> ::::::::::::::::::
> TYPE_31 CHAR(1)
>
> I want to have a trigger:
> If UPDATE DAILY ::::: where ON_DATE = '19990301' THEN
> UPDATE MONTHS SET TYPE_01 = :new.type;
> If UPDATE DAILY ::::: where ON_DATE = '19990302' THEN
> UDPATE MONTHS SET TYPE_02 = :new.type;
> And so on...
> If UPDATE DAILY ::::: where ON_DATE = '19990331' THEN
> UDPATE MONTHS SET TYPE_31 = :new.type;
>
> Now my trigger is :
> CREATE TRIGGER TEST_UPD
> BEFORE UPDATE ON DAILY FOR EACH ROW
> DECLARE
> tmp_date CHAR(2)
> BEGIN
> tmp_date := SUBSTR(:new.on_date,7,2);
> IF tmp_date = '01' THEN
> UPDATE MONTHS SET TYPE_01 = :new.type WHERE ...... ;
> END IF;
> IF tmp_date = '02' THEN
> UPDATE MONTHS SET TYPE_02 = :new.type WHERE ...... ;
> END IF;
> :::::::::::
> IF tmp_date = '31' THEN
> UPDATE MONTHS SET TYPE_31 = :new.type WHERE ...... ;
> END IF;
> END;
>
> This is absolutely not a good trigger with 31 IF.
> I'm looking for more efficiency solution.
> If you have any idea,I'll very appreciate for it.
> Thank you in advance.
>
> Violin.
> violin.hsiao_at_mail.pouchen.com.tw
Received on Wed Mar 31 1999 - 03:15:08 CST