Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Stored Procedures triggered by state

    I am interested in creating a stored procedure that will trigger a script if an insertion to a particular table is done. I have never done this before, so I was wondering if someone could share any advice.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You mean like CREATE TRIGGER?

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    105
    Yes, actually tested a trigger by doing an insert into a another table.

    But, is it possible to execute a vbscript or external script from a trigger?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, but I've never done it, nor would I recommend it...

    Why don't you tell us what the proccess you're trying to automate is....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2004
    Posts
    105
    We currently have an application that the staff uses to create Projects for Accounting Purposes and other Project management stuff. Everything associated with that Project is stored into a MSSql database.

    Well, at the same time that a Project is created in the application. I want a Project Directory to be created in the network filesystem.

    The creation of the Directory is done already in a stand alone script that I wrote. However, it takes parameters such as a ProjectName and Location.

    It would be ideal if I had a trigger that would pass the necessary values into this script if a new project was created from the application.

    So, that's why I was wondering if it is all possible to run a script from a trigger.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd just plop the VBscript into a DTS package, then execute the DTS package from within the trigger... Then again, I'm a lazy bum too.

    -PatP

  7. #7
    Join Date
    May 2004
    Posts
    105
    Ok, that's good to know.

    Thanks for the advice.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    When the application creates the "Project" why not just do it then?

    Are you using stored procedures or some othe application language?

    In T-SQL you can use xp_cmdhell 'MD C:\myDir99'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation, but if the script is relatively simple I'd probably just re-code it in Transact-SQL within the trigger. While I'm pretty big on code re-use, it also doesn't pay to go "round red robin's barn" for a one-liner. I guess you'll need to make a call depending on how complex the script is.

    -PatP

    sniped! Yeah, what Brett said.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Just an observation, but if the script is relatively simple I'd probably just re-code it in Transact-SQL within the trigger. While I'm pretty big on code re-use, it also doesn't pay to go "round red robin's barn" for a one-liner. I guess you'll need to make a call depending on how complex the script is.

    -PatP

    sniped! Yeah, what Brett said.
    Except I wouldn't put it in a trigger...I'd marry the logic with the process that creates the project...hopefullt it's a sproc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    May 2004
    Posts
    105
    I suppose since the script is not that difficult I could use something like this:
    xp_cmdhell 'MD C:\myDir99'

    But since I don't have much experience with packages how would I execute my package from a trigger that is dependent on an Insert into the XYZ table?

    I am interested in knowing this if I ever have to do it in the future.

    Thanks.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't help thinking you are asking for trouble but bundling XP_CMDSHELL into a trigger. I wouldn't give an unthinking database trigger such automated and unfettered control over your file system. Next thing you know, the machines will rise up against us and overthrow our civilization.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As Brett and blindman observed, it would be better if the code could be housed in a stored procedure instead of a trigger for a lot of reasons. One problem that you run into with a trigger that you don't have in a stored procedure is the need to handle more than one row at a time, which leads to the need for a cursor. Another issue is that triggers need to deal with all kinds of relational issues (performance wise, as well as considering the implications of what the trigger does), which aren't issues for the faint of heart.

    With that said, you could use something like:
    Code:
    CREATE TRIGGER tiXYZ ON dbo.XYZ FOR INSERT AS
    
    DECLARE @cCmd NVARCHAR(999)
    ,  @cProject NVARCHAR(50)
    
    DECLARE ztiXYZ_projectname CURSOR FOR SELECT
       projecname
       FROM inserted
    
    OPEN ztiXYZ_projectname
    FETCH ztiXYZ_projectname INTO @cProject
    
    WHILE 0 = @@fetch_status
       BEGIN
          SET @cmd = 'mkdir \\targetServer\targetShare\targetpath\' + @cProject
          EXECUTE master.dbo.xp_cmdshell @cCmd
          FETCH ztiXYZ_projectname INTO @cProject
       END
    
    CLOSE ztiXYZ_projectname
    DEALLOCATE ztiXYZ_projectname
    
    RETURN
    -PatP

  14. #14
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    Yeah, doing what PatP suggests is probably the best alternative. Just take note of the fact that when the xp_cmdshell is executed, it is executed with the privileges the account that the SQL-Server service is running on, in case you get access violations ...
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  15. #15
    Join Date
    May 2004
    Posts
    105
    Yes, as a matter of fact I am getting a permission denied on xp_cmdshell. What do I do to fix this?

Posting Permissions

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