Link to home
Start Free TrialLog in
Avatar of finance_teacher
finance_teacher

asked on

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
SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of finance_teacher
finance_teacher

ASKER

ID is the ONLY PK I have.
Please post updated code.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
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
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.