question

milani-3147 avatar image
0 Votes"
milani-3147 asked MelissaMa-msft commented

The ways of Alerting when comparing two tables columns and find differences..



Hello Good Morning Guru's

Can you please help me to give some ideas or links that I can use it in my situation.

I have two tables, both tables have EMPID as primarykey.

Here DailyStageTable is truncate and get loads daily.where as FinalTable is the main table it has all data only appends no deletion happens in this FinalTable

1) FinalTable (EMPID, Col1,Col2, Col3, Col4, Col5, Col6, Col7, InsertDate, UpdateDate)
2) DailyStageTable (EMPID, Col1,Col2, Col3, Col4, Col5, Col6, Col7)

so here, I have a daily job that executes the procedure, the procedure logic does 2 things

1) insertion of new records from DailyStageTable to FinalTable (based on EMPID) also populates Insertdate (getdate() of procedure execution)
2) updates the remain values Col1,Col2,Col3,Col4,Col5,Col6, Col7 if any of these values were differet between both tables for an EMPID.


so far it looks good, but now I should not allow utomatic updates for Col3, Col6 (using the update statement in procedure) instead I need to alert with the data in it so the update will handle manually by some person..

so here my question is any idea on "ALERT" thsee two columnc changes if any?

what could be best possible ways to do it, this can be an email or stage them in some table or in same table...

please Advise..

Asita

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered milani-3147 commented

Hi @milani-3147,

Please have a try with below:

 IF EXISTS (SELECT 1 FROM FinalTable a inner join DailyStageTable b on a.col1=b.col1 and a.col2=b.col2 
 where a.col3<>b.col3 or a.col6<>b.col6)
 BEGIN
     RAISERROR ('Please update the col3 or col6 manually', -- Message text.  
         16, -- Severity.  
         1 -- State.  
         )
   --or send email alert
    EXEC msdb.dbo.sp_send_dbmail
       @profile_name = '{% Audit.Print Mail_Profile %}',
       @recipients = '{% Audit.Print Mail_Recipients %}',
       @body = '{% Audit.Print Mail_Body %}',
       @subject = '{% Audit.Print Mail_Subject %}';
 END
 ELSE
 BEGIN
     -- update statement here
 END

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much,

this is good. 

but we are looking to set up something like writing to a table so the user can query it and change manually. the reason for this manual process is there is some quite more columns for each table so just checking to see if there is any better way rather than sending multiple emails into email box for each set of table for each column? any suggestions please.?


Thank you a ton in advance
Sincerely
asita

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered milani-3147 commented

You likely do not want to stop the processing when col3 or col6 is changed.

You will want to "log" those changes to another table in your proc, and use an SSRS report or another job to email or show those changes to someone else.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Tom.

Thank you for your response. correct we dont want to process the col3, col6 changed.

would like to log this in such a way (which is i am looking for possible ways loading to table or in the same table something with new column stating the columns need to check with column names etc)

any help please?

Thank you
Asita

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

It is difficult to give good suggestions without having the context. To me this sounds like there has to be UI where responsible users can review possible changes. This also means that the tentative changes have to be diverted to another table, so that users can make this review.

It goes without say that this is quite an increase in complexity compare to a plain and unconditional update.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @milani-3147 ,

Please have a try with below example:

Step 1: create one intermediary table.

  create table Intermediary 
  (EMPID  int,
  col3 varchar(10),
  col6 varchar(10))

Step 2: modify the update statement.

  IF EXISTS (SELECT 1 FROM FinalTable a inner join DailyStageTable b on a.EMPID=b.EMPID 
  where a.col3<>b.col3 or a.col6<>b.col6)
  BEGIN
      RAISERROR ('Please update the col3 or col6 manually', -- Message text.  
          16, -- Severity.  
          1 -- State.  
          )
      insert into Intermediary
      select a.EMPID,a.col3,a.col6 from DailyStageTable a inner join DailyStageTable b on a.EMPID=b.EMPID 
      where a.col3<>b.col3 or a.col6<>b.col6)
  END
  ELSE
  BEGIN
     --update statement here
  END

Step 3: grant or revoke the user access of Intermediary table.

 GRANT SELECT, INSERT, UPDATE, DELETE ON Intermediary TO User1;
 REVOKE INSERT, UPDATE, DELETE ON Intermediary FROM User2,User3;

Step 4: create trigger on the Intermediary table.

 CREATE TRIGGER MYTRIGGER ON Intermediary
 AFTER UPDATE AS 
 BEGIN
 UPDATE A
 SET A.COL3=B.COL3, A.COL6=B.COL6,A.UpdatedDate=GETDATE()
 FROM FinalTable A
 INNER JOIN inserted B ON a.EMPID=b.EMPID 
 WHERE a.col3<>b.col3 or a.col6<>b.col6
 END

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you much Melissa. I will try this keep you post.

Thank you all gurus.

0 Votes 0 ·

Hi @milani-3147 ,

Thanks for your update.

Please let me know if any update. Thanks.

Best regards,
Melissa

0 Votes 0 ·