Databases
StackOverflow2013
Postsdbo
POSQL trigger with parsing and existence check on another table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL trigger with parsing and existence check on another table
    primarykey
    data
    text
    <p>So I have a work order table with 3 fields relevant to the question being asked here (dbo.workorder with some sample values):</p> <pre><code>location | supervisor | ownergroup ABC-123 | JSMITH | ALPHA XYZ-987 | JDOE | OMEGA ABC-123 | NULL | NULL XYZ-987 | NULL | NULL </code></pre> <p>The last two rows are to show that sometimes the supervisor/ownergroup is not filled out when inserting a workorder row which leads to the question! I have another table called "roles" with rows such as below:</p> <pre><code>role | value ABCSupervisor | JSMITH ABCOwnergroup | ALPHA XYZSupervisor | JDOE XYZOwnergroup | OMEGA </code></pre> <p>As you can see from the "roles" table, a role is made up of the first 3 letters of the location (ALWAYS) plus the word Supervisor or Ownergroup. When the 3rd and 4th rows of the "workorder" table are inserted, I'd like to develop a trigger that would attempt to fill in the values for Supervisor/Ownergroup IF there is a match in the "roles" table. If there isn't a supervisor/ownergroup for that location prefix, then it should default to a set value (let's say supervisor='super' and ownergroup='og'). Here's what I have so far, though perhaps a different approach would be better:</p> <pre><code>CREATE TRIGGER [dbo].[OwnergroupSupervisor] ON [dbo].[workorder] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; UPDATE wo SET wo.ownergroup= (THIS IS WHERE I NEED HELP) FROM dbo.workorder AS wo INNER JOIN inserted AS i ON wo.wonum=i.wonum WHERE wo.ownergroup IS NULL END GO </code></pre> <p>I'm guessing an IF EXISTS or a CASE of some sort? Probably involving something like LEFT(wo.location,3)+'Ownergroup' perhaps?</p> <p>Any and all help is greatly appreciated! Thanks! <hr> Clarification: wonum is the primary key for the "workorder" table. I am joining the "inserted" table (which contains the rows that are being inserted or updated) to the "workorder" table so that I am only updating the new/updated rows and not the entire "workorder" table. Nothing in my question right now has anything to do with the "roles" table. See update 1 below for a better understanding of what I'm doing... <hr> Update 1:</p> <p>I've worked out one solution, but if there is nothing in the "roles" table, it just leaves the supervisor/ownergroup null instead of changing it to a default value. I can deal with this for now, but would like a better option. Here's what I have:</p> <pre><code>CREATE TRIGGER [dbo].[OwnergroupSupervisor] ON [dbo].[workorder] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; UPDATE wo SET wo.ownergroup=(SELECT value FROM roles WHERE role=LEFT(wo.location,3)+'Ownergroup') FROM dbo.workorder AS wo INNER JOIN inserted AS i ON wo.wonum=i.wonum WHERE wo.ownergroup IS NULL UPDATE wo SET wo.supervisor=(SELECT value FROM roles WHERE role=LEFT(wo.location,3)+'Supervisor') FROM dbo.workorder AS wo INNER JOIN inserted AS i ON wo.wonum=i.wonum WHERE wo.supervisor IS NULL END </code></pre> <p>I have not worked out what would happen if two rows were found in the roles table (although I'm pretty sure the application restricts this so it should be OK). But as I mentioned above, this just keeps the supervisor/ownergroup NULL if no role is found in the roles table. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USD.R.
    1. USD.R.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO1st step - rebuild your tables to keep logically separate data in separate columns - i.e. keep the `ABC` portion in a separate column (in both tables).
      singulars
      1. POSQL trigger with parsing and existence check on another table
      1. USDamien_The_Unbeliever
    2. CO@Damien_The_Unbeliever: Unfortunately this is not an option because the database structure is dictated by the application (IBM's Maximo asset management platform).
      singulars
      1. POSQL trigger with parsing and existence check on another table
      1. USD.R.
    3. COIf it's not a database under your control, would it not be better to implement view(s) that make your queries work, and leave the underlying tables in control of the (mad) system that doesn't populate them properly?
      singulars
      1. POSQL trigger with parsing and existence check on another table
      1. USDamien_The_Unbeliever
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload