none
Need ID of after updated row to use in Trigger

    Question

  • Hi,

    I have a strange but hopefully straight forward question.

    I need to run a stored procedure on a row ID (ID (is the PK) is parameter to SP) that has just been updated in a particular column. The table is constantly being updated but i only want to run the SP if this particular column "WorkFlowDate" has been timestamped/updated.

    I'm guessing i use a trigger to get the row ID by using the inserted and deleted tables and then store that ID in a variable.  Next i can pass that variable to the SP.

    Am i going along the correct path or completely wrong.... ?Basically i need to run a SP from third party tool (Switch) by passing a parameter but i'm struggling with the syntax, so i thought this would be an alternative - run an update statement from Switch and then run the SP on the trigger in SQL on the row thats been stamped in Switch.

    All make sense??  Help!!

    Sunday, June 30, 2013 4:53 PM

Answers

  • DatabaseText:"Dbname":SQL=" EXEC MyStoredProc [JobName.Proper] ",[Connection="DSN"]
    
    

    DatabaseText:"Dbname":SQL=" MyStoredProc([JobName.Proper]) ",[Connection="DSN"]

    Jobname proper is the ID of the row in question but it says syntax error near ). 

    I'm not sure how to specify the parameter when calling a SP - with or without brackets or quotes etc...

    I can only guess here since I'm not familiar with Switch.  A example T-SQL execute statement passing a integer literal parameter by ordinal would be:

    EXEC MyStoredProc 1
    

    I would expect the corresponding Switch syntax to be like:

    DatabaseText:"Dbname":SQL=" EXEC MyStoredProc 1 ",[Connection="DSN"]
    

    Now the tricky part is how Switch recognizes and replaces variable values.  Do you do the same for your working update statements?  If so, you might be able to pattern the needed execute statement similarly.  Perhaps something like:

    DatabaseText:"Dbname":SQL=" EXEC MyStoredProc [JobName.Proper] ",[Connection="DSN"]

    But if JobName.Proper is a string value, you might need to enclose the value in quotes (unless Switch does that for you:

    DatabaseText:"Dbname":SQL=" EXEC MyStoredProc '[JobName.Proper]' ",[Connection="DSN"]


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 30, 2013 9:42 PM

All replies

  • The first consideration with trigger development is to make sure it can handle multiple rows.  A trigger fires once per statement and a single statement can affect multiple rows.

    To execute a proc from a trigger for each row updated, you will either need to use a cursor on the inserted table or change the proc to accept a list of IDs.  A list can be passed as a table-valued parameter, with the table variable loaded from the inserted table.

    A better approach might be to do the set-based processing inside the trigger instead of calling a proc.

    I don't understand what you mean about calling the proc from the third-party Switch tool.  Are you saying you can modify the tool to call a proc directly but are just struggling with the syntax?  Perhaps you can provide more details as that might be the best approach.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 30, 2013 5:07 PM
  • ok let me explain further.

    Switch is an automation tool.  Switch can accept SQL statements to interact with a database.  I have already used Update and query join statements which work fine.

    This time however i need to call a SP for a given job ID (PK) from within switch and i dont know the correct syntax.  It goes something like this:

    DatabaseText:"Dbname":SQL=" MyStoredProc([JobName.Proper]) ",[Connection="DSN"]

    Jobname proper is the ID of the row in question but it says syntax error near ). 

    I'm not sure how to specify the parameter when calling a SP - with or without brackets or quotes etc...

    but calling the SP through switch is definitely the best route.

    hope this makes sense...

    thanks

    Sunday, June 30, 2013 9:17 PM
  • DatabaseText:"Dbname":SQL=" EXEC MyStoredProc [JobName.Proper] ",[Connection="DSN"]
    
    

    DatabaseText:"Dbname":SQL=" MyStoredProc([JobName.Proper]) ",[Connection="DSN"]

    Jobname proper is the ID of the row in question but it says syntax error near ). 

    I'm not sure how to specify the parameter when calling a SP - with or without brackets or quotes etc...

    I can only guess here since I'm not familiar with Switch.  A example T-SQL execute statement passing a integer literal parameter by ordinal would be:

    EXEC MyStoredProc 1
    

    I would expect the corresponding Switch syntax to be like:

    DatabaseText:"Dbname":SQL=" EXEC MyStoredProc 1 ",[Connection="DSN"]
    

    Now the tricky part is how Switch recognizes and replaces variable values.  Do you do the same for your working update statements?  If so, you might be able to pattern the needed execute statement similarly.  Perhaps something like:

    DatabaseText:"Dbname":SQL=" EXEC MyStoredProc [JobName.Proper] ",[Connection="DSN"]

    But if JobName.Proper is a string value, you might need to enclose the value in quotes (unless Switch does that for you:

    DatabaseText:"Dbname":SQL=" EXEC MyStoredProc '[JobName.Proper]' ",[Connection="DSN"]


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 30, 2013 9:42 PM
  • >>Am I going along the correct path or completely wrong..? <<

    Dr. Codd and I vote completely wrong :) What is a row_id? An identifier is for a particular set of entities, not the physical storage location on one disk for one schema. You have done what noobs always seem to do. First, never learn basic RDBMS concepts, then mimic in SQL the old, familiar, magnetic tape, sequential files with a fake record number. 

    What your vague narrative describes is classic FORTRAN II. We had to local registers (your local variable) with SUBROUTINE parameters to call them. Constant updating would simply add timestamped rows to a history table, and a VIEW would give the current state.  

    Since noobs do not understand declarative or set-oriented programming, you go for triggers and really bad procedures (while, if-then-else, recursion, etc) so that you can keep writing BASIC, or COBOL in T-SQL dialect. It will not port, be a bitch to maintain and run like glue. But you stay in your mental comfort zone. Oh, triggers in T-SQL are table level constructs, not row level; that is possible in ANSI/ISO Standard SQL. 

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, June 30, 2013 10:56 PM
  • Dan - absolute genius.  The SP has started to work from within Switch.

    I just need to verify the logic now.

    Thanks so much and well done to you !!

    thanks

    Monday, July 01, 2013 7:57 AM