Creating an AFTER trigger that prevents exceeding 250 work hours

Question

Creating an AFTER trigger that prevents exceeding 250 work hours

t-pinto

Default port

Points: 1465
More actions
December 4, 2008 at 2:05 pm

#194722

Hi, I am writing an AFTER trigger that fires whenever an UPDATE transaction executes and updates the value stored in the workHours column of the Assignment table.
The trigger checks the new value to be assigned for the hours worked but can’t exceed 250 hours.
If the new value inserted exceeds 250 hours, then the UPDATE transaction should roll back.
This is what I have so far, am I on the right track?
Thanks
Create Trigger check_work_hours
On Assignment
After UPDATE
AS
UPDATE Assignment
SET workHours = workHours
IF EXISTS (Select SUM(workHours)
From Deleted
Where SUM(workHours) >250
BEGIN
RAISEERROR (‘Value can''t exceed limit of 250’, 16, 1)
ROLLBACK TRAN
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You don't need an update in the trigger. The trigger is fired by the update. If you want to check the new values, use the inserted table. deleted, in an update trigger, contains the old values

Can't exceed 250 hours total? For 1 month?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

t-pinto Default port Points: 1465 More actions · Answer 2

Here are my modifications.

The hours can't exceed 250...total.

Create Trigger check_work_hours

On Assignment

After

AS

UPDATE Assignment

SET workHours = workHours

IF EXISTS (Select SUM(workHours)

From Inserted

Where SUM(workHours) >250

BEGIN

RAISEERROR (‘Value will exceed limit of 250’, 16, 1)

ROLLBACK TRAN

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said above, you don't need an update in the trigger. The trigger is fired by an update, there's no need to repeat that update within the trigger.

You don't use where of filter on aggregates, you use Having.

inserted only contains the rows that were affected by the update that fired the trigger. If not all rows were updated, then the sun needs to be made from the base table.

May I suggest you get your hands on a good book on T-SQL basics, or an intro to SQL training course. All the questions that you're asking are very simple

For a book, I'll recommend T-SQL Fundamentals by Itzik Ben-Gan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass