insert fucntion to trigger

Posted on 2005-04-20
Last Modified: 2010-03-19
I need to modify all my triggers wiht the following criteria inserting the
follwoing  fucntion :
vFunction = "dbo.f_ch_to_x"

Using criteria.

        select * from INFORMATION_SCHEMA.COLUMNS  
                  "where is_Nullable = 'YES'
                  "and Data_Type = 'char'
                  "and Table_Name Like 'vPER_%'
                  "order by column_name "

What will be the best way to do that.

Question by:bolicat
    LVL 20

    Expert Comment

    I'd suggest exporting all the trigger scripts out to a text file (with the drop/create syntax) then running the resulting file through any scriptable editor that you may be familiar with to insert the statement in the appropriate locations.  FInally, AFTER THROUGH REVIEW, run the resulting edited file back into SQL Server via Query Analyzer.  Be certain to keep a copy of the original script file before the edits are performed just in case something messes up.

    LVL 20

    Accepted Solution

    I forgot to mention that SQL Server doesn't let you export scripts for just the triggers by themselves... if you use EM to do this you have to select all the tables... the triggers will come along for the ride.

    There is a downloadable app that will allow you to export just the triggers... see the following link (you have to "register" with the site to get to the article, but it's free and painless... just use your email address and any password you like).

    Author Comment

    I will going to try to download the EM keep you posted.


    Author Comment

    I finally got my registration from the link suggested, what is the applicant name?
    LVL 20

    Expert Comment

    I believe the comments I provided earlier are the easiest solution to this question since there is no tool available to make the desired modifications directly to the sprocs "en masse".


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now