Can a trigger do this?

[archerks]

Can a SQL trigger do this?

Hi, I'm new to the whole SQL thing and have been reading about triggers in my SQL books. Hopefully a trigger can do the following but I have no idea on how to properly write it.

I have two tables Employee and TimeOff. (1 to many relation) They are related to each other with a field(column) named EmpID in both tables.

Whenever the value of the field named VacTaken in the TimeOff table changes I would like to update the field VacUsed in the Employee Table with the sum/total of all the VacTaken entries in the TimeOff table.

If this can be done, would someone be kind enough to post a sample of the code that could do this.

Thanks
David Shaw

 

[mrdenny]

This can very easily be done via a trigger. It would be better do be done within the stored procedure that adds the record to the TimeOff table, but it can be done with a trigger.

When writting SQL keep in mind that triggers are bad. No matter what anyone else tells you they are evil.

The reason is that they are very had to trouble shoot (since you can't run them by them selves), and very easy to mess up. They also put load on the server that you forget to account for.

That said, this should do the trick.

create trigger t_TimeOff_i on TimeOff
FOR INSERT
as
update Employee
    set Employee.VacTaken = T.VacTaken
from (select TimeOff.EmpID, sum(VacTaken) VacTaken from TimeOff where TimeOff.EmpID in (select EmpID from inserted) group by TimeOff.EmpID) T
where Employee.EmpID = T.EmpID
go

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[archerks]

Copied the code as you have, but replacing Emp:VacTaken with Emp:VacUsed.

It does not put a value into the Emp:VacUsed when I Insert a new record in the table timeoff.

What does the T in T.VacTaken represent? Also on the seventh row the T at the end of the row?

Read about stored procedures and it looks like the way to go.
If not asking to much what would the code look like if it was put in a stored procedure.

Dave

 

[mrdenny]

The T in T.VacTaken is a reference to the enbedded sub query name T. The embedded query "select TimeOff.EmpID, sum(VacTaken) VacTaken from TimeOff where TimeOff.EmpID in (select EmpID from inserted) group by TimeOff.EmpID" is named T via line 7. The T.VacTaken is pulling the value of the sum(VacTaken) from the embedded query.

If you put this in a stored procedure after your insert statement into the TimeOff table you would do an update on the Employee table. Something like this.

create procedure usp_RecordTimeOff
   @EmpID int,
   @VacTaken int
insert into TimeOff
(EmpID, VacTaken)
values
(@EmpID, @VacTaken)

update Emp
   set Emp.VacUsed = Emp.VacUsed
where EmpID = @EmpID
go

You can see why triggers are such a pain.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mrdenny]

My code was incomplete.

create procedure usp_RecordTimeOff
   @EmpID int,
   @VacTaken int
insert into TimeOff
(EmpID, VacTaken)
values
(@EmpID, @VacTaken)

update Emp
   set Emp.VacUsed = Emp.VacUsed + @VacTaken
where EmpID = @EmpID
go

You'll need to make sure that there are no Nulls in the VacUsed column in the Emp column.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[archerks]

the check syntax produces the following error....
Error 156: Incorrect syntax near the keyword "insert".

Also this looks like it produces a running total, is there a way to use the total/sum of the Column VacTaken in the Table TimeOff

 

[mrdenny]

Looks like I missed the keyword as. Apparently I just couldn't type yesterday.

create procedure usp_RecordTimeOff
   @EmpID int,
   @VacTaken int
as
insert into TimeOff
(EmpID, VacTaken)
values
(@EmpID, @VacTaken)

declare @TotalVacTaken int

set @TotalVacTaken = (select sum(VacTaken) from TimeOff where EmpID = @EmpID)

update Emp
   set Emp.VacUsed = @TotalVacTaken
where EmpID = @EmpID
go

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[archerks]

Changed the code as follows to remove the syntax errors..Also the EmpID was changed to EmpNum and Table TimeOff changed to Absence to match names in the database.

CODE CODE CODE
CREATE TRIGGER [T_VacSickTotal] ON dbo.Absence
FOR INSERT, UPDATE, DELETE
AS
Declare @EmpNum int,
@VacTaken int

insert into Absence
(EmpNum, VacTaken)
values
(@EmpNum, @VacTaken)

declare @TotalVacTaken int

set @TotalVacTaken = (select sum(VacTaken) from Absence where EmpNum = @EmpNum)

update Employee
set employee.vacused = @TotalVacTaken
where EmpNum = @EmpNum
*******************
This code produces the follow error on a Record Insert (similar errors on Update and Delete)
Attempts to automatically number this record have failed
File:dbo,Absence
Key:Abs:KeyRefNum
Error: Cannot insert the value NULL into column 'RefNum', table'ETIabsence.dbo.Absence'; column does not allow nulls. INSERT fails [2300]. Try Again?

The 'RefNum' is a column that just numbers each record using auto increment.

Thanks
Dave

 

[archerks]

This Trigger works on the Insert and Update but not on the delete. Anyone know how to make it work for a delete also?

CREATE TRIGGER [UpdateVacUsed] ON [dbo].[Absence]
FOR INSERT, UPDATE, DELETE
AS
Update Employee
set Employee.VacTaken = T.VacTaken
from (select EmpNum, sum(VacTaken) VacTaken from Absence
where EmpNum in (select EmpNum from inserted) group by EmpNum) T

Where Employee.EmpNum = T.EmpNum

 

[mrdenny]

Delete uses a different virtual table. It's called deleted not inserted. You can either create two triggers one for insert and update and one for delete, or modify the inline query.

Update Employee
    set Employee.VacTaken = T.VacTaken
from (select EmpNum, sum(VacTaken)  VacTaken from Absence 
where EmpNum in (select EmpNum from inserted) or EmpNum in (select EmpNum from deleted) group by EmpNum) T

Where Employee.EmpNum = T.EmpNum

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)