Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Trigger -- based on Process# / Field combo

What is the proper
"after UPDATE" TRIGGER so below works ?

Do you have any freeware tools
to make TRIGGER creation easier.
------------------------------------------
CREATE TRIGGER Test1
   ON  MAINT_WORK_REQ
   AFTER UPDATE
AS
BEGIN

--below syntax is not FULLY correct
    if old.ProcessNumber = 1
      && new.S1Est=new.C1Est,
    then new.ProcessNumber = 6

     else if old.ProcessNumber = 6
       && new.ActionTake IS NOT NULL
       && new.Signature IS NOT NULL
    then new.ProcessNumber = 7
0
finance_teacher
Asked:
finance_teacher
  • 3
  • 3
  • 3
3 Solutions
 
knightEknightCommented:
You may not be able to do that in an AFTER UPDATE trigger because the "old" data is already gone.   You may need to do it in an INSTEAD OF UPDATE trigger.

... in which case, assuming that S1Est is a key on the table,  the syntax would be something like this:


   update M
      set ProcessNumber = 6
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.S1Est = M.S1Est
   where M.ProcessNumber = 1


   update M
      set ProcessNumber = 7
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.S1Est = M.S1Est
   where M.ProcessNumber = 6
       and INSERTED.ActionTake IS NOT NULL
       and INSERTED.Signature IS NOT NULL
0
 
knightEknightCommented:
The above assumes that S1Est is a key on the table, if this is not the case, please post the name(s) of the primary key column(s) and I will adjust the code accordingly.
0
 
finance_teacherAuthor Commented:
ID is the ONLY PK I have.
Please post updated code.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
knightEknightCommented:
-- again, this is an approximation meant for an INSTEAD OF UPDATE trigger, and based on the information provided.

   update M
      set ProcessNumber = 6
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.ProcessNumber = 1
      and I.S1Est = M.S1Est

   update M
      set ProcessNumber = 7
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.ProcessNumber = 6
       and I.ActionTake IS NOT NULL
       and I.Signature IS NOT NULL
0
 
Scott PletcherSenior DBACommented:
>> You may not be able to do that in an AFTER UPDATE trigger because the "old" data is already gone. <<

Not true.  The "deleted" pseudotable holds the row as it existed before it was updated.  The actual table itself naturally has the new version of the row for an AFTER trigger (or the "inserted" pseudotable contains the new row(s) as well).



CREATE TRIGGER Test1
   ON dbo.MAINT_WORK_REQ
   AFTER UPDATE
AS

UPDATE MWR
SET ProcessNumber = CASE WHEN d.ProcessNumber = 1 THEN 6 ELSE 7 END
FROM dbo.MAINT_WORK_REQ MWR
INNER JOIN deleted d ON
    d.##key_col## = MRW.##key_col## --<-- put your key col(s) here
WHERE
    (d.ProcessNumber = 1 AND MWR.S1Est = MWR.C1Est) OR
    (d.ProcessNumber = 6 AND MWR.ActionTake IS NOT NULL AND WMR.Signature IS NOT NULL)
0
 
finance_teacherAuthor Commented:
Below trigger currently emails only ONE person.

How can I setup so below COMMENTED out
code works, sending to multiple users ?

I just want it easy since I have 10+ WorkFlowStages for each
of my 20+ ProgramID, making 200+ possible statements
   if WorkFlowStage = __
   and ProgramID = ___
   then change WorkFlowStage to ___
   and email multiple people
-------------------------------------------------------

CREATE TRIGGER workflow
ON maint_work_req
      AFTER UPDATE, INSERT
AS
BEGIN

update M
      set WorkFlowStage = 6
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID = 'BUICK'

declare @sub1 varchar(100)
set @sub1 = 'johnDoe@test.com'
            /*
              select up.email
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where RoleName = 'BUICK' + '_EMAIL_6'
              --where RoleName = M.ProjectID + '_EMAIL_6'
              and up.email is NOT NULL
             */
            EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'SABmail',
           @recipients  = @sub1,
           @body = 'This is now in stage 6.....',
           @subject = 'Automated Success Message';
---------------------------------------------------------------
update M
      set WorkFlowStage = 7
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID <> 'FORD'

declare @sub2 varchar(100)
set @sub2 = 'BobSmith@test.com'
            /*
              select up.email
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where RoleName = 'FORD' + '_EMAIL_7'
              --where RoleName = M.ProjectID + '_EMAIL_7'
              and up.email is NOT NULL
             */
            EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'SABmail',
           @recipients  = @sub2,
           @body = 'This is now in stage 7.....',
           @subject = 'Automated Success Message';
     
END
GO
0
 
Scott PletcherSenior DBACommented:
You can use the OUTPUT clause to get data from the rows that were UPDATEd:

create table #email_table (
    id int not null,
    ...
)

update M
      set WorkFlowStage = 6
OUTPUT M.ID, ... INTO #email_table
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID = 'BUICK'

Then send emails based on the columns in the OUTPUT results.
0
 
finance_teacherAuthor Commented:
How can I get "@recipients  = @sub2" to include whatever email address(es) appear in the below ?

              select up.email
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where RoleName = 'FORD' + '_EMAIL_7'
              --where RoleName = M.ProjectID + '_EMAIL_7'
              and up.email is NOT NULL
0
 
Scott PletcherSenior DBACommented:
I don't know, I don't know the data in those tables.

I don't know what columns feed from the MAINT_WORK_REQ table to the emails.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now