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 *************** =========
================================
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