SQL & PL/SQL

Hi everyone,
i have around 100 tables and each table contains thses columns row_created_by,row_changed_by,row_created_date,row_changed_date along with other columns and primary key column generating using squencer

i created trigger for respective tables

create or replace
TRIGGER "TABLE_IU_TRG" BEFORE INSERT OR UPDATE ON TABLE_NAME
FOR EACH ROW
BEGIN
If inserting then
If :new.TABLE_NAME_ID is NULL THEN
SELECT TABLE_NAME_ID_SEQ.NEXTVAL INTO :NEW.TABLE_NAME_ID FROM DUAL;
end if;
if :new.ROW_CREATED_DATE is NULL then
:new.ROW_CREATED_DATE := SYSDATE;
end if;
if :new.ROW_CREATED_BY is NULL then
:new.ROW_CREATED_BY := USER;
end if;
if :new.ROW_CHANGED_DATE is NULL then
:new.ROW_CHANGED_DATE := SYSDATE;
end if;
if :new.ROW_CHANGED_BY is NULL then
:new.ROW_CHANGED_BY := USER;
end if;
if :new. COL2 is NULL then
:new.COL2 :=sys_guid();
end if;
If updating then
:new.ROW_CHANGED_DATE := SYSDATE;
:new.ROW_CHANGED_BY := USER;
:new.COL2 :=sys_guid;

end if;
END;

above mentioned trigger is same for all 100 tables.

my requirement is insted of make more of the IU trigger common by calling a procedure? i.e., having a way to dynamically generate them,since they are almost identical.it will be easy to make a change in one place(the generator) and re-generate them.
please suggest me .

Thanks

Karteek

on Apr 10, 2012 2:49 AM