Trigger: BEFORE INSERT

Question

How to check if a DATE being inserted or updated in a table is between two other dates from another table.

Adicional Info: I have 2 tables:

Activity:

StartDate date NOT NULL
EndDate date NULLABLE

SubActivity:

SubActivityDate date NOT NULL

When EndDate IS NOT NULL I check if: StartDate ≤ SubActivityDate ≤ EndDate

When EndDate IS NULL I check if: StartDate ≤ SubActivityDate

I was trying to write a BEFORE INSERT trigger but I figured out that it doesnt exist.

So what I could do?

AFTER INSERT?
INSTEAD OF INSERT? looks better than 1st solution
Is it possible just with CHECK Constraints?

How do I solve this problem?

EDIT

I just went with the CHECK constraint + function:

constraint:

ALTER TABLE SubActivity
    ADD CONSTRAINT CK_SubActivity_Date CHECK (dbo.ufnIsSubactivityDateValid(ActivityID, SubActivityDate) = 1);

function:

CREATE FUNCTION ufnIsSubactivityDateValid(@ActivityID [int], @SubActivityDate [date])
RETURNS [bit]
AS
BEGIN
    DECLARE @StartDate date, @EndDate date;

    SELECT @StartDate = StartDate , @EndDate = EndDate 
    FROM Activity
    WHERE ActivityID = @ActivityID;

    IF (@SubActivityDate < @StartDate )
        RETURN 0; -- out of range date

    IF (@EndDate IS NULL)
        RETURN 1; -- good date
    ELSE
        IF (@SubActivityDate > @EndDate)
            RETURN 0; -- out of range date

    RETURN 1; -- good date
END

`inserted` and `deleted` are tables so that they can represent the results of set operations. Designing a trigger on the assumption that they will always process exactly one row is generally a bad plan. If you are absolutely certain there will never be more than one row then _please_ add a check for the number of rows and use `RaIsError` or `Throw` to explicitly inform those who come along later that they have attempted to execute an unacceptable statement. (`if ( select Count(*) from inserted ) > 1 RaIsError( 'FooTable_Insert: No more than one row may be processed.', 25, 42 ) with log`) — HABO, Apr 01 '15 at 14:35
In a **INSTEAD OF UPDATE** the `inserted` and `deleted` may have multiple results right? But in an **INSTEAD OF INSERT** the `inserted` allways only has 1 row, right? Thanks for that number of rows verification code. Another question: In an **INSTEAD OF UPDATE** how can I check if it is being updated by PK. (If I can check it, I know for sure there will be only 1 row) — Fábio Silva, Apr 02 '15 at 14:04
The only guarantees on numbers of rows: an `INSERT` statement will not cause `deleted` to be populated and a `DELETE` statement will leave `inserted` empty. Otherwise the tables may contain more than one row. ([Ref.](https://msdn.microsoft.com/en-us/library/ms191300.aspx)) You could use [UPDATE()](https://msdn.microsoft.com/en-us/library/ms187326.aspx) to check if a PK (or any other column) is being updated. (Note that the value returned depends only on assignment to the column, the actual value(s) may not have be altered.) What, pray tell, does updating a PK mean to you? — HABO, Apr 02 '15 at 16:45
I didnt say that I want to update the PK. What I was trying to say was that I want to ensure that the table is being updated **by** the PK. e.g `UPDATE SubActivity SET SubActivityDate = '2015-10-10' WHERE SubActivityID = 10;` **Where `SubActivityID` is the PK of SubActivity table** — Fábio Silva, Apr 02 '15 at 17:06
You could ensure that all of the rows in, say, `inserted` _happen_ to have the same value for a column, e.g. `SubActivityId`. That may just be a coincidence and you can't be certain since you don't have access to the triggering statement within the trigger. — HABO, Apr 02 '15 at 17:47
In a trigger there is a way to know the `where part`: UPDATE SubActivity SET SubActivityDate = '2015-10-10' `WHERE SubActivityID = 10`; So that I can raise errors if not the PK column? — Fábio Silva, Apr 02 '15 at 21:31
How do you get the statement within the trigger? I'm not aware of `GetStatementClause( 'WHERE', @@TRIGGERING_STATEMENT )`. Even if it existed you would need to be able to parse the expression and determine that it is limited to a single `SubActivityID` value even if it checks other columns, uses subqueries, ... . Or do you have access to the execution plan for the triggering statement in some useful sense? Perhaps I'm just not understanding your PK requirement. — HABO, Apr 02 '15 at 21:39

score 0 · Answer 1 · answered Apr 01 '15 at 01:52

What is best is situation by situation. Constraint guarantees proper values but rollsback an entire transaction over one wrong value. Triggers allow you more control but are a little more complex because of it.

Create and Populate your Table

IF OBJECT_ID('dbo.yourTable') IS NOT NULL
    DROP TABLE yourTable;

CREATE TABLE yourTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    StartDate DATE NOT NULL,
    SubActivityDate DATE NULL,
    EndDate DATE NULL
);

INSERT INTO yourTable(StartDate,SubActivityDate,EndDate)
VALUES  ('20150101',NULL,NULL),
        ('20150101',NULL,NULL),     
        ('20150101',NULL,'20150201'),
        ('20150101',NULL,'20150201');

Constraint Method:

ALTER TABLE yourTable
ADD CONSTRAINT chk_date CHECK (StartDate <= SubActivityDate AND SubActivityDate <= EndDate);

UPDATE yourTable
SET SubActivityDate =   CASE
                            WHEN ID = 1 THEN '20140101' --bad
                            WHEN ID = 2 THEN '20150102' --good
                            WHEN ID = 3 THEN '20140101' --bad
                            WHEN ID = 4 THEN '20150102' --good
                        END

SELECT *
FROM yourTable;

Since there is at least value that does not fit the constraint, the whole transaction is rolled back and the result is that SubActivitDate stays NULL.

Results:

ID          StartDate  SubActivityDate EndDate
----------- ---------- --------------- ----------
1           2015-01-01 NULL            NULL
2           2015-01-01 NULL            NULL
3           2015-01-01 NULL            2015-02-01
4           2015-01-01 NULL            2015-02-01

Trigger Method(My Preferred Method)

CREATE TRIGGER trg_check_date ON yourTable
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE yourTable
    SET SubActivityDate =   CASE
                                WHEN inserted.SubActivityDate >= inserted.StartDate AND ((Inserted.EndDate IS NULL) OR Inserted.SubActivityDate <= Inserted.EndDate) THEN inserted.SubActivityDate
                                ELSE NULL
                            END
    FROM yourTable
    INNER JOIN inserted 
    ON yourTable.ID = inserted.ID
END;
GO

UPDATE yourTable
SET SubActivityDate =   CASE
                            WHEN ID = 1 THEN '20140101' --bad
                            WHEN ID = 2 THEN '20150102' --good
                            WHEN ID = 3 THEN '20140101' --bad
                            WHEN ID = 4 THEN '20150102' --good
                        END

SELECT *
FROM yourTable

This method allows the proper values and simply returns null for improper ones. If you wanted, you could even export the incorrect values from the inserted table into a log table so you know which ones didn't work. Or raise an error message and list the values that didn't work. In short, you have total control of the situation.