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.
[email protected]

Apart from the table design -do you really need this, it is badly denormalized,
don't want to criticize you though- the only option here seems to use the
dbms_sql package.
skeleton code:
declare
cur_handle integer;
dbms_sql_feedback integer;
sqlstr varchar2(1000);
begin
sqlstr := 'update ....'etc;
cur_handle := dbms_sql.open_cursor;
dbms_sql.parse(cur_handle,sqlstr,dbms_sql.native);
dbms_sql_feedback := dbms_sql.execute(cur_handle);
dbms_sql.close_cursor(cur_handle)
end;
There is a dbms_sql.bind procedure, you should be able to use host variables in
your statement, though you probably won't really need it in this particular case.

Hth,

Sybrand Bakker, Oracle DBA

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.