Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Trigger question

    Hi all,
    How would one create a trigger to fire off a DTS package on an insert into a table?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try to run a job(DTS) from a trigger.

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    What do you mean? Creating a Job that runs DTS and running the Job from trigger? How would I do that?

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    exec msdb.dbo.sp_start_job @job_name='your job name here'

  5. #5
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Another question then. Lets say someone is copying records from there Access table to my SQL table. Will that trigger the INSERT? If so if its multiple records that are getting moved, will it trigger it multiple times or just once?

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    once for all inserted, and if it's for update then for all updated, and the same for deleted.

  7. #7
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thank you, I'll try it.

  8. #8
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    What function would I use to see all my Triggers?

  9. #9
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    sp_helptrigger or select name from sysobjects where type = 'TR'.

    BTW, you can also directly run DTS package within trigger by calling 'DTSRUN' utility.

  10. #10
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    OK, I just tryed starting the job from trigger:

    CREATE TRIGGER DTSSAUpdateStart
    ON tblGlobalDirUpdate
    FOR INSERT
    AS
    exec msdb.dbo.sp_start_job @job_name='SAGlobalDirUpdate'
    GO

    My Job does this:
    DTSRun /~Z0x8B831DECBF54609D046E02E784443AD2920A1E12D2897E FC02FE343B248DE6F30FB258DDEBC1D166D7E3828B83E15E61 D25F3301E1A095BF436830383848A2B9282D4423BA68918ECF DCFA11569C26CC2155A501751661A3D39B6525074699DD66BA 690211694DE04EE9C5C3726F37E74905B69E0FF30A56FAE40A 08632A82B215598AB28CDA75A277EFF81DD7159CFB61B8DD2D 580E8A08E5782F8E9B41E1623C99E2D041E24F128C3F63

    Now when that other person is trying to copy there records over, they get an error the Job does not exist.
    Question 1: Why do they get that error in Access?
    Question 2: The Job Does Exist, why and how to correct this? The login there using, should it have any special right?

    Thank you
    Last edited by bpolunin; 02-02-04 at 15:32.

  11. #11
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    How would I do that rding?

  12. #12
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    you can try:

    exec master..xp_cmdshell 'DTSRUN /E /Npackage_name'


    Check dtsrun in BOL for more details.

  13. #13
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Should the person copying the records have any special privileges with there login?

  14. #14
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    The user account who deals with inserts must have INSERT permission to the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •