Using trigger to perform data check and allow UPDATE/INSERT to continue if successful

Hi

I would like to use a trigger to do some data integrity checks. For instance, I have a table with an ID, StartDate and EndDate columns.

I now want to create a trigger to prevent StartDate from ever being greater than EndDate. I want to do this in the database, because there are multiple applications accessing this databases and I want to prevent having to change all of them.

I know how to use an INSTEAD OF trigger to check that StartDate < EndDate and then INSERT/UPDATE if the check is ok, but this requires:
(1) having two different triggers for UPDATE and INSERT, while this simple check can easily be performed in the same way for both and
(2) hard-coding the column names in the trigger, which means that any time a column is added or changed, I have to change the triggers (INSERT and UPDATE) as well.

I could also use a AFTER trigger and specify ROLLBACK if the check fails, but this causes a nasty "The transaction ended in the trigger. The batch has been aborted." to any calling application when all I want is my very user-friendly "Start date must be smaller than end date" anytime the check fails.

So the behaviour that I really want is:
- UPDATE or INSERT
- Trigger fires
- Dates are checked
- If dates are ok, just proceed with what you were doing Mr SQL batch
- If dates are not ok, stop right there and return the error (and only the error) @Err

Any way of doing this?

I may be mistaken, but I think I used to be able to do this with a FOR trigger in SQL 2000.Start Free Trial