|
madd0g17
Starting Member
8 Posts |
 Posted - 2010-10-21 : 14:37:04
|
I am working with an IBM Maximo database that issues workorders automatically. When the workorders are issued (aka Inserted into the database workorder table), I would like to auto-assign a supervisor, owner, and owner group based on a set of criteria. This only needs to happen IF the supervisor, owner, and owner group aren't already assigned. Often times a "parent workorder" has the information, but it needs to be copied into the "child" workorders (as you will see in the criteria below). The criteria for ALL of the triggers is:WHERE status<>'COMP'AND historyflag=0AND istask=0Here is the criteria for the trigger:-If the Owner Group and Supervisor have a value, skip the record. (Do nothing)-If the Owner Group and/or Supervisor is blank or null, and the corresponding PARENT Work Order field is not Null, copy the Owner Group and/or Supervisor from the PARENT Work Order record.-If the Parent Work Order Owner Group and/or Supervisor is blank or null, then assign the Owner Group and Supervisor per the table values below: (I have removed names for security's sake, but all the columns are correct, i.e. B3 is supposed to have SuperA as the supervisor)Site/ OwnerGroup/ Supervisor ABC / @ABCGroup / @ABCSupervisorDEF / @DEFGroup / @DEFSupervisorGHI / @GHIGroup / @GHISupervisoretc... **NOTE:SITE is not a table column, it is really the first 3 characters of the workorder.location field. For example, the location could be ABC-1234, meaning it is at site ABC, building 1234 (though these are NOT separate values, it's combined). In this SQL query, all buildings at a location are serviced by the same ownergroup/supervisor, so all other queries we currently use are using workorder.location='ABC%'I've done plenty of selects, updates, and stored procedures, but this is my first trigger and want to make sure I don't royally screw up the database! Any and all help is greatly appreciated!For those unfamiliar with Maximo, the table is: dbo.workorder and the relevant fields are: wonum (primary key) - Example: XYZ98765location - Example: ABC-1234ownergroup (should be @ABCGroup, a variable specified earlier)supervisor (should be @ABCSupervisor, a variable specified earlier)parent (contains the wonum of a parent workorder, NULL if it IS the parent)haschildren (0 if it's a child workorder, 1 if it is a parent workorder)Here is what I would like the logic to look like for the final query:If the supervisor field is missing, first look to see if it has a parent, and if so, does the parent have a supervisor? If not, assign based on the table above.If the ownergroup field is missing, first look to see if it has a parent, and if so, does the parent have an ownergroup? If not, assign based on the table above.This is why I am thinking a case statement maybe the best option. Also, I currently have a list of variables such as "@ABCSupervisor, @DEFSupervisor, @GHISupervisor,...etc" so that I can change them in the future if need be. To save a lot of redundant code, is it possible to do something like:**NOTE:in this example, location is ABC-1234, ownergroup SHOULD be @ABCGroup, supervisor SHOULD be @ABCSupervisor, where @ABCGroup and @ABCSupervisor are set earlier in the codeIf the supervisor field is missing, first look to see if it has a parent, and if so, does the parent have a supervisor (then copy it's supervisor)? If not, assign supervisor X.where X = '@' + '(the first three characters of the location)' + 'Supervisor' (so in this example, X=@ABCSupervisor) Is this possible??? If not, what is the most efficient way to write this trigger?? It will be triggered on any insert or update on the workorder table. |
|
SQL Trigger for Auto-Assigning Values (Maximo)