Using Triggers To Enforce Constraints

Question

I'm trying to enforce integrity on a table in a way which I do not think a Constraint can...

CREATE TABLE myData
(
   id             INTEGER IDENTITY(1,1) NOT NULL,
   fk_item_id     INTEGER               NOT NULL,
   valid_from     DATETIME              NOT NULL,
   invlaid_from   DATETIME              NOT NULL
)

The constraint I want to apply is that there should never be any entries for the same "fk_item_id" with overlapping dates.

Note:

invalid_from is the instant immediately after the valid period.
This means that the following two periods are fine...

• '2008 Jan 01 00:00' -> '2008 Feb 01 00:00' (All of Jan)
• '2008 Feb 01 00:00' -> '2008 Mar 01 00:00' (All of Feb)

I can check that rule in a trigger. When the trigger does find an illegal insert/update, however, what is the best way to prevent the "illegal" inserts/updates from happening?

(If inserted includes two valid records and two invalid records, can I stop just the two invalid records?)

Cheers,
Dems.

EDIT:

In the case I had above, a constraint using a function worked well. But I never worked out why the RAISERROR didn't work in the trigger version.

I thought it was because the trigger is an AFTER trigger, and that I'd need a BEFORE trigger, but that doesn't appear to be an option...

Answer 1

You can't delete directly from inserted (updates to the logical tables raise an error) but you can join back to the source table as seen below

create table triggertest (id int null, val varchar(20))
Go
create trigger after  on [dbo].triggertest
for Update  
as
Begin

    delete tt from triggertest tt inner join 
    inserted i on tt.id = i.id 
    where i.id = 9

End
GO
insert into triggertest values (1,'x')
insert into triggertest values (2,'y')
Update triggertest set id = 9 where id = 2
select * from triggertest
1, x

Also, you don't have to go the trigger route, you can also bind a check constraint to the return value of a function

Alter table myData WITH NOCHECK add 
Constraint  CHK_VALID CHECK (dbo.fx_CheckValid(id, valid_from , invalid_from) = 1 );

Answer 2

Raise an error.

Answer 3

Your trigger could modify the valid_from and/or invlaid_from DATETIMEs of the "illegal" records to special values. A subsequent cleanup step could identify the "illegal" records.

Answer 4

Don't delete the records from the inserted table... that's silent failure. The road to hell is paved in Partial Commits.

You need to RAISERROR, which is essentially what a constraint would do.