sql trigger on insert

Question

quick question, I am building a simple trigger which purpose is to decrement the value of a table field called openSeats, the trigger is executing on insert but I dont know what commands to use to say: Decrement the value openSeats , where Id is equal to inserted Id

USE [Training]
GO
/****** Object:  Trigger [dbo].[DecrementSeat]    Script Date: 11/04/2011 14:55:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

ALTER TRIGGER [dbo].[DecrementSeat]
   ON  [dbo].[personTraining]
   AFTER INSERT

AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[tbl_training]
    SET openSeats = openSeats - 1 
    WHERE training_id =

END

spencer7593 · Accepted Answer · 2011-11-04 20:52:12Z

2

Your trigger should be able to handle "multi-row" inserts. I expect you want to subtract 1 from openSeats for each row inserted.

Something like this:

UPDATE [dbo].[tbl_training]
   SET openSeats = openSeats - 
       ( SELECT COUNT(1) FROM inserted
          WHERE inserted.training_id = [dbo].[tbl_training].training_id )
 WHERE training_id IN
       ( SELECT inserted.training_id FROM inserted
          WHERE inserted.training_id IS NOT NULL )

edited Nov 4, 2011 at 20:52

answered Nov 4, 2011 at 20:44

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user596075 · Accepted Answer · 2011-11-04 20:35:57Z

0

Try this in your trigger:

update dbo.tbl_training
set openSeats = openSeats - 1
where training_id in
(
    select training_id
    from inserted
)

It utilizes the inserted dynamic table that SQL Server populates with inserted data during a trigger.

answered Nov 4, 2011 at 20:35

user596075

4 Comments

user959443 Over a year ago

The column prefix 'inserted' does not match with a table name or alias name used in the query

user959443 Over a year ago

thanks , got it working, can you recommend me a good tutorial or book for triggers?

user596075 Over a year ago

Here's a great article on triggers to get you started (msdn.microsoft.com/en-us/magazine/cc164047.aspx). Any decent book on SQL Server will have good information as well. If this solved your answer, please mark as answer. Glad to help!

spencer7593 Over a year ago

This statement will not handle multi-row inserts appropriately. When an insert statement inserts more than one row for the same training_id, the openSeats will be decremented by one, rather than by the number of rows inserted for that training_id. Ref: msdn.microsoft.com/en-us/library/ms190752.aspx

Collectives™ on Stack Overflow

sql trigger on insert

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related