Create trigger that checks for a date from another table-CodePudding

I have to write a trigger that checks that the MaintenanceDate is greater or equal to the BirthDate of the Plant (which is in another table). I already wrote a function that brings the birthday of the Plant by Id.

I'm having problems If I insert a bunch of items in the maintenance table, the trigger is just not doing the job right.

CREATE TRIGGER TrgCheckBirthDateOfPlant
ON Maintenance
INSTEAD OF INSERT
AS  
    DECLARE
         @PlantId AS int,
         @MaintenanceDate AS date;
BEGIN
    SELECT @PlantId = PlantId 
    FROM inserted;

    SELECT @MaintenanceDate = MaintenanceDate 
    FROM inserted;

    IF (@MaintenanceDate >= dbo.GetBirthDateById(@PlantId))
        INSERT INTO Maitenance 
            SELECT I.PlantId, I.MaintenanceDate, I.description, I.type  
            FROM inserted I;
END

The tables involved are:

CREATE TABLE Maintenance
(
    id int IDENTITY PRIMARY KEY,
    PlantId int 
         REFERENCES Plant(id) NOT NULL,
    MaintenanceDate datetime NOT NULL,
    description varchar(250),
    type varchar(15)
);

CREATE TABLE Plant
(
    id int IDENTITY PRIMARY KEY,
    name varchar(30) NOT NULL,
    birthDate NOT NULL,
    height decimal (6, 1) CHECK (height<= 12000)
    price decimal(10, 2) CHECK (price > 0),
);

CodePudding user response：

There are a few things to include in a trigger. Returning extra counts is not a good thing in general. (I am not sure what count is returned for an instead of trigger. The rule to include the following statement might be something to investigated. We would not want both the original insert and the replacement counts. Does SQL Server suppress the count for the original insert if there's an instead of trigger?)

SET NOCOUNT ON

A test if there is nothing to do. @@ROWCOUNT (or the bigint version) is not reliable for this anymore because the source can be a merge statement. If the trigger fires on deletes, then testing the "deleted" table is also needed. The first statement below is good enough for inserts. The second is okay for inserts, updates, and deletes. (Don't use both.)

IF NOT EXISTS(SELECT * FROM inserted) RETURN -- no rows inserted or updated
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) RETURN -- no rows inserted, updated, or deleted

Now each record in inserted needs to be tested. It appears each can be from a different plant. I would check the logic in the function and avoid the function. It can be a performance killer to use a scalar function in a set based query. Perhaps something like this.

INSERT INTO Maintenance 
SELECT I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM inserted I
LEFT JOIN Plant p
ON p.ID = I.PlantId
WHERE I.MaintenanceDate >= p.BirthDate;

For testing, you don't have an "inserted" table. You can use the real table. Don't forget to exclude the insert. Test it on a good sample of the data to insure you get the results you desire. I imagine the existing data has dates that will be selected.

SELECT Top 100 I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM Maintenance I
LEFT JOIN Plant p
ON p.Id = I.PlantId
WHERE I.MaintenanceDate >= p.BirthDate;