DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Create triggers using spooled file....

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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'

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    set trimspool off
    Jeff Hunter

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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
  •  


Click Here to Expand Forum to Full Width