-
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.
Please suggest
Thanks
Ronnie
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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.
If I set it to 1000 then its too big.
Is there a way to handle this.
Thanks
Ronnie
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
-
Originally posted by marist89
set trimspool off
Hi,
Even with set trimspool off I get an output as following
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;
/
what am I doing wrong here.
Thanks
Ronnie
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|