If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hi,
I am trying to create triggers for all the tables in my schema which have a created_date column.

Since there are 100's of tables which have that column I wrote a small sql statement which will generate the create trigger statement for me.

This is what i am doing....

select 'Create or replace Trigger '||table_name||'_Trigger BEFORE INSERT OR
UPDATE ON '||
table_name||' for each row BEGIN IF inserting THEN
:NEW.created_date:=sysdate; :NEW.created_by:=user; END IF; If Updating
then :NEW.updated_date:=sysdate; :NEW.updated_by:=user; end if; END; /'
from dba_tab_columns where column_name='CREATED_DATE'

I spool the above query into a file and execute it.

It would work fine , but the only problem is the / at the end of the
procedure it creates. The / should appear on the next line and I am not able
to do so. Is there a way I can force a carriage return before the /.

If i manually edit the output of the above query so that the / appears on
the next line of each result set, it works great.

select 'Create or replace Trigger '||table_name||'_Trigger BEFORE INSERT OR
UPDATE ON '||
table_name||' for each row BEGIN IF inserting THEN
:NEW.created_date:=sysdate; :NEW.created_by:=user; END IF; If Updating
then :NEW.updated_date:=sysdate; :NEW.updated_by:=user; end if; END;||chr(10)||'/'
from dba_tab_columns where column_name='CREATED_DATE'

Originally posted by pando select 'Create or replace Trigger '||table_name||'_Trigger BEFORE INSERT OR
UPDATE ON '||
table_name||' for each row BEGIN IF inserting THEN
:NEW.created_date:=sysdate; :NEW.created_by:=user; END IF; If Updating
then :NEW.updated_date:=sysdate; :NEW.updated_by:=user; end if; END;||chr(10)||'/'
from dba_tab_columns where column_name='CREATED_DATE'

Thanks Pando,

This works great.

But how do i set the linesize.
If I leave it default it goes to the next line after the 80th column and then it does not compile properly.

Create or replace Trigger ADDRESS_Trigger BEFORE INSERT OR UPDATE ON ADDRESS fo
r each row BEGIN IF inserting THEN :NEW.created_date:=sysdate; :NEW.created_by
:=user; END IF; If Updating then :NEW.updated_date:=sysdate; :NEW.updated_by:=
user; end if; END;
/

Create or replace Trigger AFFILIATES_Trigger BEFORE INSERT OR UPDATE ON AFFILIA
TES for each row BEGIN IF inserting THEN :NEW.created_date:=sysdate; :NEW.crea
ted_by:=user; END IF; If Updating then :NEW.updated_date:=sysdate; :NEW.update
d_by:=user; end if; END;
/

Create or replace Trigger COM_Trigger BEFORE INSERT OR UPDATE ON ANURAG for

each row BEGIN IF inserting THEN :NEW.created_date:=sysdate; :NEW.created_by:=
user; END IF; If Updating then :NEW.updated_date:=sysdate; :NEW.updated_by:=us
er; end if; END;
/

Create or replace Trigger API_OIL_STATISTICS_Trigger BEFORE INSERT OR UPDATE ON
API_OIL_STATISTICS for each row BEGIN IF inserting THEN :NEW.created_date:=sy
sdate; :NEW.created_by:=user; END IF; If Updating then :NEW.updated_date:=sysda
te; :NEW.updated_by:=user; end if; END;
/