Re: trigger help

From: <amitabh12_at_my-deja.com>
Date: 2000/05/02
Message-ID: <8enf1d$tld$1@nnrp1.deja.com>#1/1

Hi,

I wrote the following procedure.. See if it makes sense to you..If not drop me an email..

I put this under Post-text-item trigger

PROCEDURE emp_audit IS
BEGIN   /***

     If the record status is 'changed' or 'insert' perform the following
     operation.

  ***/

  IF :SYSTEM.RECORD_STATUS = 'CHANGED' OR :SYSTEM.RECORD_STATUS = 'INSERT'
          THEN
  BEGIN
             :GLOBAL.table_name := GET_BLOCK_PROPERTY
(:SYSTEM.CURRENT_BLOCK,DML_DATA_TARGET_NAME);

     	   IF :GLOBAL.old_data <> 'new_entry'
     	   	THEN
     	   	:GLOBAL.old_data   := GET_ITEM_PROPERTY


(:SYSTEM.CURRENT_ITEM,DATABASE_VALUE);

     	   END IF;

    	SELECT userenv('sessionid')
    	INTO :GLOBAL.sessionid
    	FROM dual;
    	SELECT terminal,machine,osuser
    	INTO :GLOBAL.terminal, :GLOBAL.machine, :GLOBAL.osuser
    	FROM v$session
    	WHERE audsid = :GLOBAL.sessionid;
    	IF :GLOBAL.old_data = 'new_entry'
    		THEN
    		INSERT INTO emp_audit
    	  VALUES

(:GLOBAL.table_name,:SYSTEM.CURRENT_ITEM,USER,SYSDATE,:GLOBAL.old_data,

                  :SYSTEM.CURSOR_VALUE,:emp1_ssno,:emp1_year,:GLOBAL.osu ser,:GLOBAL.machine,

                  :GLOBAL.terminal,:SYSTEM.CURRENT_FORM,'New_employee_ad ded');

    	  :GLOBAL.old_data := 'no_data_yet';
    	ELSE
    		IF :GLOBAL.old_data IS NULL OR :GLOBAL.old_data
<> :SYSTEM.CURSOR_VALUE
    			THEN
    			INSERT INTO emp_audit
    			VALUES

(:GLOBAL.table_name,:SYSTEM.CURRENT_ITEM,USER,SYSDATE,:GLOBAL.old_data,

                    :SYSTEM.CURSOR_VALUE,:emp1_ssno,:emp1_year,:GLOBAL.o suser,:GLOBAL.machine,

                    :GLOBAL.terminal,:SYSTEM.CURRENT_FORM,'Employee_info _changed');

    		END IF;
    	END IF;
  	END;

  END IF; END; In article <8en5dt$dbc_at_nntpb.cb.lucent.com>,   "Gary Knopp" <gknopp_at_ascend.com> wrote:

> I would like to create a generic procedure that
> writes a tables column name and value to
> a history table if the value has changed.
>
> Is there anyway to do that without creating
> an if-endif block for each column value.  I
> don't want to do the following.
>
>          if (:old.col1 <> :new.col1) then
>                     -- insert into history table
>          end if
>
>          if (:old.col2 <> :new.col2) then
>                     -- insert into history table
>          end if
>
> I would like the procedure to be general enough that
> I don't have to modify it when I add/delete columns from
> a table that calls the procedure though a trigger.
>
> Thanks
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 02 2000 - 00:00:00 CDT