Reputation: 23
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),
);
Upvotes: 0
Views: 937
Reputation: 72090
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 TABLE dbo.TwoRows (dummy int);
INSERT dbo.TwoRows DEFAULT VALUES;
INSERT dbo.TwoRows DEFAULT VALUES;
CREATE VIEW dbo.CheckBirthDateOfPlant
WITH SCHEMABINDING -- must be schame-bound
AS
SELECT 1 AS dummy
FROM dbo.Maintenance m
JOIN dbo.Plant p ON p.Id = m.PlantId
CROSS JOIN dbo.TwoRows
WHERE m.MaintenanceDate < p.birthDate;
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.
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.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;
Upvotes: 1
Reputation: 1143
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;
Upvotes: 1