How to trigger event INSERT for custom table (staging for ServiceNow acknowledge/close)

Hello guys,

I'm almost at the tail end of completing this end-to-end process for ServiceNow I'm working on (I think)...but I need some assistance triggering the process on INSERT (does not work). I noticed there are "Triggers" defined in SQL for each table, so maybe I'm answering my own question by stating that none exist under my custom table (created through db schema). I figured I'd include screenshots of my process orchestration just to make sure I have everything else in place? If the logic in SQL is all I need to get the Event to work, then I need some guidance on how to script that...is there another table I can baseline from that is generic for what I need? Also, I am provisioning an entitlement that is UNSItemB (called TEST_APP_X). The request for this item goes to EX approval (to ServiceNow), then request needs to be closed when my custom process is completed successfully (entitlement assigned to requestor)...is this the way to do it? Any support always appreciated as we are now in QA/testing for this feature/functionality!! THANKS!!

Kenny

ServiceNow Configuration - Custom Process_INSERT_Event.docx

  • Hi Kenneth,

    hmm. that's strange. In our scenarios the events are working ootb, if we extend our schema with "SchemaExtension.exe" (Quest v6). Here's an example from the DDL, maybe it helps...

      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

      TABLESPACE "QIMDBE"  ENABLE

       ) SEGMENT CREATION IMMEDIATE

      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

    NOCOMPRESS LOGGING

      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

      TABLESPACE "QIMDBE"

    You can proof the event behavior of a processes very well with the "ObjectBrowser.exe". Please check the generating condition of your process, too.

    If the process isn't generated (or simulated) with the INSERT event, then I would suggest to open a support call, because your process design looks error free at first sight.

  • Hello Kenneth,

    that's correct: The system raises the default events INSERT, UPDATE and DELETE automatically - for custom tables too!

    The condition for that is: The changes must come over the object layer. All direct SQL statements in the table will not trigger any events.

    Dumb question: Did you compiled the db after the implementation of your process?

    If yes you should do the suggested: Start the ObjectBrowser, open an object in your table "TQA_ServiceNow" and look which events are existing.

    There you could trigger an event manually - this should cause the generation of your process:

    TriggerEvent.jpg

    To the 2nd question:

    We have a similar function in the default - you can use this as an example.

    There is a possibility to request NEW AD groups in the ITShop.

    In the decision workflow an "EX" step is contained which starts a process to create the requested group: the process "VI_ESS_PersonWantsOrg create AD group"

    The last step of this process is the closure of the request using a "CallMethod" job:

    EXProcess.jpg

    Regards,

    Steffen

  • Thanks guys, yeah, with the custom webservice I'm using it is inserting directly to SQL db...so that's why the Event is not working (as Carsten also noted for me). I also noticed in SQL there is no trigger for the custom table, but if I create one for INSERT that probably won't matter either huh? This is the part where I'm stumped. Based on the OOB scripts, I can see how to insert at the object layer within Q1M with iSingleDbObject or SingleDbObjectSnapshot...but not sure how to do it from a web application that's not a part of Q1IM (or if that's possible). Would you happen to know of a template or example script I can baseline from? Thanks again for your guidance...my head is spinning from trying to figure this out...

    Kenny

  • Hello Kenny,

    have you tried the stored procedure vid_insertforhandleobject_0 or vid_insertforhandleobject_freeze? These procedures create a job in the jobqueue witch is executed by the object layer.

    exec vid_insertforhandleobject_freeze 'INSERT', 'MyCustomTable, '', 'Column1', 'Value1', 'Column2', 'Value2', @ProcId='123'

    Regards,

    Steffen

  • Hey Steffen,

    Not yet, but that looks like something I can use!! I actually figured out how to use the process planner and schedule and was able to enter the conditions to get my process going. I have a split process that checks "OrderState" in PwO to make sure request hasn't been aborted or cancelled, on TRUE continue...and am using the HandleObjectComponent - CallMethod to "MakeDecision" now...it is commited and compiled but the closed tickets aren't being resolved for some reason...I'm sure it's probably a setting or parameter I need to fix...but looks like the ball is rollin now!!

    Thanks everyone for your help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Kenny

  • Hi Kenny,

    when you insert the records directly via SQL you would need a db trigger on this table which fires the needed event after every insert.

    I attach an example trigger for exactly this purpose.

    Regards,

    Steffen

  • Hey Steffen, just wanted to thank you again!!!! This trigger was EXACTLY what I needed to complete my solution end-to-end!!! I GREATLY APPRECIATE YOUR HELP!!!

  • Hi Steffen,

    Just one more thing...everything works GREAT for this process!!...but when the INSERT/UPDATE trigger kicks off and my update script runs, it seems there are a couple of processes that just sit there (ScriptExec TRUE and DELETE...x 2). I have tried to dumb down the ScriptExec process step as much as possible even just using Sub instead of function and script does what it's supposed to do successfully (update PwO). Do you know if there's a process step I'm missing to close out the process so it just doesn't sit there after completion? I initially had my script as a function returning TRUE and boolean paramater value = TRUE for success enabled...but it does the same thing no matter what I change. Thanks again for your help!

    ServiceNow_47.jpg

  • Hi Kenny,

    when a process was executed completely the last step will be changed to "DELETE" or "HISTORY".

    As you know we have exactly ONE Jobservice in the database which is marked as "Master SQL server".

    Additional to the execution of jobs (for his queue) it's the job of the Master SQL server to DELETE the finished processes from the JobQueue or MOVE them to the JobHISTORY (it depends on the state of the finished jobs).

    So if you have the situation that the jobs are staying in the Queue the reason must be: The Master SQL server is not running.

    Steffen

  • Thanks!!! I'll double check the sql server and services.