Friday, 22 February 2013

Call the sequence and WHO columns in trigger

Create WHO Columns  Through Function :
================================

function GET_USER
return varchar2
is

   l_procedure          varchar2(30) := 'GET_USER.';
   l_stage              varchar2(200);

   l_user               varchar2(240);
 
begin

   select substr(v('USER'),1,30)
     into l_user
     from dual;
       
   if l_user is null then
      l_user := 'UNKNOWN';
   end if;
  
   return (l_user);
         
exception
   when others then
        return ('ERROR');

end GET_USER; 


 Create sequence :-
===================


CREATE SEQUENCE APEXDEV.PA_TASKS_NUM_S
  START WITH 1008
  MAXVALUE 999999999999
  MINVALUE 1001
  NOCYCLE
  NOCACHE
  NOORDER;

Write Trigger :-
=================

CREATE OR REPLACE TRIGGER APEXDEV.PA_TASKS_BI
  BEFORE INSERT ON APEXDEV.PA_TASKS   FOR EACH ROW
DECLARE
  l_user  VARCHAR2(30)   := GET_USER;

BEGIN

  --Get ID from Sequence
  select PA_TASKS_S.nextval
    into :new.TASK_ID
    from dual;
  -- Get Task Number from Seruence
  select PA_TASKS_NUM_S.nextval
    into :new.TASK_NUMBER
    from dual;
   
  --Assign Default Values 
  :new.created_by        := l_user;
  :new.creation_date     := sysdate;
  :new.last_updated_by   := l_user;
  :new.last_update_date  := sysdate;
 
  --:new.start_date        := nvl(:new.start_date,sysdate);
 
END PA_TASKS_BI;



===========         **********   END   ***************          =========





No comments:

Post a Comment