Re: Help: How to do the UPDATE Trigger?

From: Philippe <parnaud_at_yahoo.com>
Date: Wed, 31 Mar 1999 09:59:26 +0200
Message-ID: <7dskma$j27$1@concorde.ctp.com>

Use DBMS_SQL Package for dynamic sql

CREATE TRIGGER TEST_UPD
BEFORE UPDATE ON DAILY FOR EACH ROW
DECLARE
   tmp_date CHAR(2);

   strSql         VARCHAR2(100);
   curs            INTEGER;
   dummy       NUMBER;

BEGIN
    tmp_date := SUBSTR(:new.on_date,7,2);     strSql := 'UPDATE MONTHS SET TYPE_' ¦¦ tmp_date ¦¦ ' = ''' ¦¦ :new.type ¦¦ '''';

    curs := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curs,strSQL,DBMS_SQL.NATIVE);     dummy := DBMS_SQL.EXECUTE(curs);
    DBMS_SQL.CLOSE_CURSOR(curs);

    EXCEPTION

        WHEN OTHERS THEN
           DBMS_SQL.CLOSE_CURSOR(curs);

END; HTH Philippe

Violin wrote in message <37058cb6.9863292_at_news.twsc.pouchen.com.tw>...
>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 - 01:59:26 CST