none
Execute a stored procedure on Insert Trigger

    คำถาม

  • Ok. I have gotten burned on a trigger that should have worked 100% fine as it did in testing, but when put into production failed and of course did not perform the INSERT as it should. I know this to be how it works, but I am trying to figure out a work around. I have a stored procedure that just calulates sums from a table and UPDATE/INSERT if the sum is greater than the existing data. I want it to perform this stored procedure upon every INSERT to update as required. I call it a stored procedure, but truth is its not one yet. Right now I have it in a trigger, but I cannot risk losing any more production data. I was just wondering, could I migrate it to a stored procedure and then execute the stored procedure in my trigger? Well I know I can do that, but I was wondering, would doing it that way allow for the insert to continue despite a failure in a stored procedure?

    Example:

    1. Upon INSERT  -> 2. Trigger  -> 4. Complete INSERT
                                         |-> 3. Stored Procedure

    If 3 failes inside the stored procedure and not at the point of calling the executing of the stored procedure, will 4 continue without a hitch or will in rollback on me and throw an error?

    16 เมษายน 2555 14:27

คำตอบ

  • If the stored procedure called from a trigger fails, then the whole insert will fail and rows will be not inserted. So, calling SP from the trigger is risky. AFAIK, there is no way to insert rows and do some extra work without caring if that extra work succeeds or not. So, it's either everything or nothing.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • ทำเครื่องหมายเป็นคำตอบโดย mkruluts 16 เมษายน 2555 16:30
    16 เมษายน 2555 16:26
    ผู้ดูแล

ตอบทั้งหมด

  • If your stored procedure is a single-record stored procedure, this sounds like a performance nightmare for a trigger; here is why:

    Triggers must be written to handle multiple record cases.  Writing a single record stored procedure means that records in the trigger must then also be handled one record at a time; this is a very bad idea.

    Instead, you need to give full details of what exactly you are trying to do so that we ca help you to a better design.



    16 เมษายน 2555 14:38
    ผู้ดูแล
  • It would be helpful when you would post your trigger code.. Do you use the INSERTED virtual table correctly to determine the number of inserted rows?
    16 เมษายน 2555 14:40
  • To paint the picture, I have a production table that has details of the product (for this specific scenario I am using the weight calculations stored in the table along with various other information). I also have a production table that stores the "Best Shift" for the production for the current product. When a record is inserted, currently my trigger goes to the start of the turn and sums the weights of all of the product and then searches the "Best Shift" table. If it exists, it compares and will update if greater. If it doesn't exist, it'll insert the appropriate record. Right now this works great and has been in production for 6 months. However, the start times are hard coded and we recently changed the start times. Now, to combat future start times, a web application was made for simple update of a record for each application using specific start times. I am now trying to integrate this into my trigger where it goes to the table and gets the start times to use in the trigger.

    In testing it works fantastic, but so has other triggers I have written and production has proven otherwise. So there is no currently known issue with my trigger code, I am trying to do a preemptive strike with avoiding lost production data. Also, this is a manufacturing "assembly" line environment, which means I run zero risk of multiple record inserts. Regardless, nothing from the insert is being passed as a parameter to the stored procedure, so I don't really understand why that would influence it in any way other than performance, and I don't see SUM() of maybe on a good day 200 records at maybe on a good day 3 minute intervals being a big issue.

    I'm not tring to come off as an ungreatful jerk here, nor am I looking for someone to fix something that is broken. Because nothing is broken at the current time and I honestly appreciate responses from people. However, my initial question still stands and has yet to be acknowledged... Will failure inside of a stored procedure result in a failure to commit the insert to the database upon which is using the trigger?

    16 เมษายน 2555 15:12
  • Is it possible to use instead the same stored procedure from both your application and the web application to do all the work in one SP instead of delegating work into a trigger?

    I suggest you to read this blog post, it seems very similar to your scenario

    T-SQL Tuesday #21 - A trigger can do that!


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    16 เมษายน 2555 15:22
    ผู้ดูแล
  • Thanks, but the web application just makes a gui to update the record in the table with a specific application name. This is needed because direct database updates are against procedures within my organization using tools such as Microsoft Database Management Studio and production data. So the result is a pretty web application that uses domain access to restrict permissions and update the shifts. The table it updates contains only ShiftStartTime1, ShiftStartTime2, ShiftStartTime3, ApplicationName, and FirstShiftBeforeMidnightBIT. Other applications within the organization use these start times. One is a production display that shows hourly breakdown and also the best shift and on what date for production of the current product (that information is stored in another table). This best shift information is populated currently by a trigger. I can just go in and change the start times in the trigger and it continue to work like clockwork, or I could tie it into this other table. My concern is, when I start to get more stuff going on, it may increase the likely-hood of failure. This cannot happen.
    16 เมษายน 2555 15:35
  • Can you do best shift population as a job that runs independently on the other work done with the tables but frequently enough?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    16 เมษายน 2555 15:52
    ผู้ดูแล
  • The data has to be up to date in real time. I could have just written an SSIS package or something if I planned on doing a scheduled job. I was trying to do something with less load, but now I don't know what the solution is... Can I take all of this ignoring of my initial question to be a subtle way of telling me that what I am askinig about a stored procedure will not work?

    16 เมษายน 2555 16:20
  • If the stored procedure called from a trigger fails, then the whole insert will fail and rows will be not inserted. So, calling SP from the trigger is risky. AFAIK, there is no way to insert rows and do some extra work without caring if that extra work succeeds or not. So, it's either everything or nothing.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • ทำเครื่องหมายเป็นคำตอบโดย mkruluts 16 เมษายน 2555 16:30
    16 เมษายน 2555 16:26
    ผู้ดูแล