I have to Implement a trigger that check The start date and known trainer uniquely identify-CodePudding

My SQL Server trigger:

CREATE OR ALTER TRIGGER TR_Check_Trainers_Can_Start_Only_One_Course_A_Time
ON offr
AFTER INSERT, UPDATE
AS 
BEGIN 
    IF @@ROWCOUNT = 0
        RETURN

    SET NOCOUNT ON

    BEGIN TRY
        IF EXISTS (SELECT 1 
                   FROM offr AS o 
                   INNER JOIN inserted AS i ON i.trainer = o.trainer
                   WHERE o.starts = i.starts)
            THROW 500001, 'This trainer can start only one course a time' , 1
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
END

I have this data for the trainer 1017 in table offr:

AM4DP   1997-09-06  CONF    6   1017    SAN FRANCISCO
AM4DP   2004-03-03  CONF    8   1017    DALLAS
AM4DPM  1997-09-03  CONF    6   1017    SAN FRANCISCO
APEX    1997-08-15  CONF    6   1017    SAN FRANCISCO
DBCENT  1997-08-20  CONF    6   1017    SAN FRANCISCO
J2EE    1997-08-14  CONF    6   1017    SAN FRANCISCO
PLSQL   1997-08-16  CONF    6   1017    SAN FRANCISCO
RGARCH  1997-08-24  CONF    6   1017    SAN FRANCISCO
RGARCH  2004-10-03  CANC    6   1017    SAN FRANCISCO
RGDEV   1997-08-28  CONF    6   1017    SAN FRANCISCO
RGDEV   2004-09-02  CONF    12  1017    HOUSTON

But when try to insert

INSERT INTO offr 
VALUES ('AM4DP', '1998-08-20', 'SCHD', 6, 1017, 'SAN FRANCISCO')

It throws an error which is in my trigger and it is not support to do that because this is no data for trainer 1017 and datum 1998-08-20

Can someone explain why?

CodePudding user response：

Because you are doing it after insert so it exists when you do the comparison.

I know this is not the question you asked but a trigger should really be your last resort here. Add a unique constraint to the table to prevent duplicates and/or update the code that does the insert to either test for existence first or does a try....catch and returns the error message.

Triggers are dangerous because they hide functionality and they can be disabled.

CodePudding user response：

Just add a HAVING to the subquery

CREATE OR ALTER TRIGGER TR_Check_Trainers_Can_Start_Only_One_Course_A_Time
ON offr
AFTER INSERT, UPDATE
AS 

IF @@ROWCOUNT = 0
    RETURN;

SET NOCOUNT ON;

IF EXISTS ( SELECT 1    
      FROM offr AS o
      INNER JOIN inserted AS i
           ON i.trainer = o.trainer
           AND i.starts = o.starts
      HAVING COUNT(*) > 1)
  THROW 500001, 'This tranier can start only one course a time' , 1;

go

A very slightly more efficient, but more complex, way of writing this is

IF EXISTS ( SELECT 1    
      FROM offr AS o
      INNER JOIN inserted AS i
           ON i.trainer = o.trainer
           AND i.starts = o.starts
      ORDER BY (SELECT NULL)
      OFFSET 1 ROW
      FETCH NEXT 1 ROW ONLY)

I see no need for the TRY CATCH as it does nothing except re-throw.

Obviously you would never actually use any of this. Instead you would use a unique or primary key constraint:

ALTER TABLE offr
    ADD CONSTRAINT pk_offr PRIMARY KEY (trainer, starts);