Solved

Write a trigger which, when the salary of any employee(s) is updated, inserts a single record into this audit table with the total amount of the change across all employees.

Posted on 2010-08-22
9
770 Views
Last Modified: 2012-05-10
I had to revise my question since people think it's different.

Table TBL_EMP_AUDIT is defined as follows:

create table TBL_EMP_AUDIT
(auditid int primary key identity(1,1),
notes varchar(200),
delta money)

 I need to write a trigger which, when the salary of any employee(s) is updated, inserts a single record into this audit table with the total amount of the change across all employees.

e.g. if five employees were given $1000 pay cut in a single update statement, the audit table should contain one row with values delta = -5000, notes = ‘Salaries updated’

I found a similar question about it on EE but they had set amounts that the salary will be reduced or added. It has to be variable.

Thanks for all your help in advance
0
Comment
Question by:flyguy80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33497449
From http:Q_26421124.html.
You noted that this was an assignment question.  That is now removed from the question text.  

There has been several discussions among the experts about doing homework assignments.  The general conclusion among the experts is to give guidance but not to actually do the homework for the person.  The purpose of homework is for them to learn how to program and if an experts does it for them, they are not learning how to do it for themselves.

Hope these give you general guidelines.  If you are stuck somewhere, shout on that specific issue.

In a trigger, you have the virtual table INSERTED that you can use a select statement on.
SUM() function can be used to get a total from a column across all rows of a table.
See Books Online for the syntax of INSERT statements, to add a record into the audit table.

To put something like £–5000, a mixture of string and numeric, you will need to concatenate two strings using the + operator.  The number needs to be converted to string using CAST(<number expression> AS varchar(10)).  But if it is just numeric, then -5000 from the SUM would be fine.

Table columns can be set up with DEFAULT constraints, which can be an expression like (getdate()) to automatically put the current date when no data is provided.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33497467
There is also a DELETED table in triggers, that you can compare against the INSERTED table. I would take a SUM from DELETED, and another SUM from INSERTED, the difference of which would be your delta.
0
 

Author Comment

by:flyguy80
ID: 33497536
Is the syntax below correct?

CREATE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP
FOR UPDATE
AS
BEGIN
DECLARE @affected_rows INT;
SELECT @affected_rows =COUNT(*) FROM deleted
DECLARE @pay_cut FLOAT;
SET @pay_cut = salary
DECLARE @delta FLOAT;
SET @delta = @affected_rows * @pay_cut

INSERT INTO  dbo.TBL_EMP_AUDIT
        (
         InsertDate,
         notes,
         delta            
          )
VALUES
        (
        GETDATE(), -- this is catch the insert time of the your update
        'Salaries updated',
        @delta
         )
END

ENABLE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:flyguy80
ID: 33497542
or this?

CREATE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP
FOR UPDATE
AS
BEGIN
DECLARE @affected_rows INT;
SELECT @affected_rows =COUNT(*) FROM deleted
DECLARE @pay_cut FLOAT;
SET @pay_cut = deleted.salary - inserted.salary
DECLARE @delta FLOAT;
SET @delta = @affected_rows * @pay_cut

INSERT INTO  dbo.TBL_EMP_AUDIT
        (
         InsertDate,
         notes,
         delta            
          )
VALUES
        (
        GETDATE(), -- this is catch the insert time of the your update
        'Salaries updated',
        @delta
         )
END

ENABLE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33497558
For the 2nd, the insert is fine (syntax), but

sample SUM statement

select SUM(moneycol) from sometable
DECLARE @affected_rows INT;
SELECT @affected_rows =COUNT(*) FROM deleted  ---- this
DECLARE @pay_cut FLOAT;
--- you cannot reference deleted.salary outside of select statements
SET @pay_cut = deleted.salary - inserted.salary   
DECLARE @delta FLOAT;

--- this logic assumes the pay cuts are all equal
SET @delta = @affected_rows * @pay_cut

Open in new window

0
 

Author Comment

by:flyguy80
ID: 33497662
I'm not understanding. Basically if I run this query:

UPDATE TBL_EMP
SET salary = salary -1000

It'll run the trigger that I wrote above which needs to sum the amount that I reduced from salary pay. If the syntax is wrong, then how do I reference the sum of all inserted - the sum of all deleted?

Like this?

CREATE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP
FOR UPDATE
AS
BEGIN
DECLARE @affected_rows INT;
SELECT @affected_rows =COUNT(*) FROM deleted
DECLARE @delta FLOAT;
SET @delta = SUM(deleted.salary) - SUM(inserted.salary)


INSERT INTO  dbo.TBL_EMP_AUDIT
        (
         InsertDate,
         notes,
         delta            
          )
VALUES
        (
        GETDATE(), -- this is catch the insert time of the your update
        'Salaries updated',
        @delta
         )
END
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33497690
select sum(salary) from deleted

This query gives you the sum of salary column across all records in the deleted table.

Also, the UPDATE statement could be complex, e.g.

UPDATE TBL_EMP SET salary = salary * 1.04

So individual records may be updated by different $ amounts, so taking the amount of 1 x number of records - that approach wouldn't work.
0
 

Author Comment

by:flyguy80
ID: 33497747
So would this work?

CREATE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP
FOR UPDATE
AS
BEGIN
DECLARE @affected_rows INT;
SELECT @affected_rows =COUNT(*) FROM deleted
DECLARE @delta FLOAT;
SET @delta = (SELECT SUM(salary) FROM DELETED) - (SELECT SUM(salary) FROM INSERTED)

INSERT INTO  dbo.TBL_EMP_AUDIT
        (
         notes,
         delta            
          )
VALUES
        (
        'Salaries updated',
        @delta
         )
END

ENABLE TRIGGER [ddltrg_AUDIT_EMP] ON dbo.TBL_EMP

UPDATE TBL_EMP
SET salary = salary -1000.00
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33497764
Depends on your definition of delta.
Normally is is current - previous, so inserted - deleted.
It looks ok otherwise.

You may want to remove @affected_rows, since it is redundant.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question