Re: Help: How to do the UPDATE Trigger?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 31 Mar 1999 06:29:47 +0200
Message-ID: <3701A4BB.7238DBCC@sybrandb.demon.nl>

Hi Violin,
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

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 Tue Mar 30 1999 - 22:29:47 CST