CODEWITHSUNDEEP
sql-servertriggers
user959443
user959443

Reputation: 97

sql trigger on insert

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

Upvotes: 1

Views: 225

Answers (2)

spencer7593
spencer7593

Reputation: 108370

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 )

Upvotes: 2

user596075
user596075

Reputation:

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.

Upvotes: 0

Related Questions