trigger that checks-CodePudding

I have to write a trigger that checks

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;

CodePudding user response：

A much better option than a trigger, is a little-known trick involving indexed views, which will enforce a multi-table constraint.

Basically, it goes as follows:

Create a table containing exactly two rows:

CREATE TABLE dbo.TwoRows (dummy int);
INSERT dbo.TwoRows DEFAULT VALUES;
INSERT dbo.TwoRows DEFAULT VALUES;

Then create a view, which contains rows which fail your constraint, but cross-join it with this table. This view will never contain any rows.

CREATE VIEW dbo.CheckBirthDateOfPlant
WITH SCHEMABINDING  -- must be schame-bound
AS
SELECT 1 AS dummy
FROM dbo.Maintenance m
JOIN Plant p ON p.Id = m.PlantId
WHERE m.MaintenanceDate < p.birthDate;

Then create an index on this view:

CREATE UNIQUE CLUSTERED INDEX CX_CheckBirthDateOfPlant
  ON dbo.CheckBirthDateOfPlant (dummy);

Now, whenever an attempt is made to insert or update rows which fail the constraint, the server will attempt to maintain this indexed view. It will feed the rows into the view's joins, then cross-join it with TwoRows. This leaves it with two rows which have the same value for dummy and therefore fail the uniqueness. The insert/update is therefore completely rolled back.

If you really want to do this as a trigger, there are numerous issues with your existing code.

An INSTEAD OF trigger can be difficult to manage, eg it would need modifying if the base table is changed. You should use an AFTER trigger instead.
You can just throw an exception and prevent the insert.
You should prevent invalid updates also.
You must take into account multiple (or zero!) rows in the trigger tables.
Scalar functions are really slow, and in this case unnecessary.

CREATE OR ALTER TRIGGER TrgCheckBirthDateOfPlant
ON Maintenance
AFTER INSERT, UPDATE
AS  

SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM inserted i
    JOIN Plant p ON p.Id = i.PlantId
    WHERE i.MaintenanceDate < p.birthDate
)
    THROW 50001, 'MaintenanceDate cannot be < p.birthDate', 0;