CODEWITHSUNDEEP
sqlsql-servertriggers
Maks Semenyuk
Maks Semenyuk

Reputation: 25

Compare inserted date with existing in trigger

I need to create trigger which will check if there is a value in the table with the same time and the same theater id

CREATE TRIGGER trCheckSameTime
ON dbo.Poster
FOR INSERT,UPDATE
AS
    IF EXISTS(SELECT P.Id_Poster
              FROM Poster P, inserted I
              WHERE P.Date = I.Date AND P.Id_Theater = I.Id_Theater)
    BEGIN 
        ROLLBACK TRANSACTION
        RAISERROR('There can''t be two spectacles at the same time in the same theater', 16, 10)
    END

I'm trying to use this, so I want it when I enter 

INSERT INTO Poster
VALUES (1,4,1,'20190420 16:00:00')
INSERT INTO Poster
VALUES (1,4,1,'20190420 16:00:00')

To trigger forbid it to do, but this trigger prohibits entering any data in the table.

3rd Value in table is theater_id and 4rth is date,

Upvotes: 0

Views: 66

Answers (1)

Ilyes
Ilyes

Reputation: 14928

You need to check before the data inserted as

CREATE TRIGGER trCheckSameTime
ON dbo.Poster
INSTEAD OF INSERT, UPDATE
AS
  IF NOT EXISTS(
    SELECT 1
    FROM dbo.Poster P INNER JOIN INSERTED I
         ON P.Id_Theater = I.Id_Theater 
            AND
            P.[Date] = I.[Date]
  )
  BEGIN
    INSERT INTO dbo.Poster(<Columns Here>) 
    SELECT <Columns Here> FROM INSERTED;
  END
    ELSE
      BEGIN
        RAISERROR('There can''t be two spectacles at the same time in the same theater', 16, 10)
      END

You can also create a constraint which is the better solution for your issue.

ALTER TABLE dbo.Poster
ADD CONSTRAINT <Constraint Name Here> UNIQUE(Id_Theater, [Date]);

Upvotes: 3

Related Questions