Update Statement with Custom Requirement

Hi Guys! hope you all doing great! Today I came up with a post on an interview question based on EMPLOYEE Table.

Task: Update Salaries of the Employees as per below requirement.

DEPTNO	INCREMENT
10	100
20	200
30	300
40	400
X0	X00

We can notice there is a correlation between DEPTNO and INCREMENT. For DEPTNO 10, its 100, DEPTNO 20, its 200, and so on.......for DEPTNO X0 its X00.

The structure of EMPLOYEE table is as below:

Input: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

Solution: Using WHILE Loop

DECLARE @INCREMENT INT = 100, @DEPTNO INT

DECLARE @TV TABLE (DEPTNO INT, FLAG BIT)

INSERT INTO @TV

SELECT DISTINCT DEPTNO, FLAG = 0 FROM EMPLOYEE

SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

WHILE (@DEPTNO IS NOT NULL)

BEGIN

            UPDATE EMPLOYEE

            SET SAL = SAL + @INCREMENT

            WHERE DEPTNO = @DEPTNO

            UPDATE @TV

            SET FLAG = 1

            WHERE DEPTNO = @DEPTNO AND FLAG = 0

            SET @INCREMENT = @INCREMENT + 100

            SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

END

Output: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

Conclusion: Compare both the screen shots and note that, the salaries are updated as per the requirement table mentioned on the top of this post. Hope this helps! Happy TSQL'ing ;)

DML Trigger to track Updates on a Specific Column

DML observations are very common these days. Its more of auditing data changes made in a table. No one knowns when and who would insert, update or delete a record in the table. The same thing applies in business perspective as well.

In order to implement an automated process to handle the data changes made to the table, we use a database object trigger, a "DML Trigger".

Illustration:

E.g. I have a table named dbo.EMP which has a column JOB that needs to be audited.

To track down the Audit, we need one more table of below structure. Lets create it.

Now coming to the major part, the trigger creation. This is a DML Update Trigger on EMP table, which writes the changes information like which Employee record, ColumnName, Valid within what date range.

This is the initial View: No Audit records.

Changing designation of EMPNO 7499 from 'Salesman' to 'Manager'.

Here are the audit records after changes written to TrgAudit table.

Changing designation of EMPNO 7499 from 'Manager' to 'Sr. Manager'.

A new record is written to TrgAudit table, with previous record updated to Expired EndDate.

Lets cross check this with changes to some other column than JOB.
I have hiked an employee's salary, so updating the SALARY column as below.

You can notice that nothing is written to the Audit table because its not the JOB column which got updated. Hope this small example on DML Trigger helps you. Happy TSQLing :)