Before Insert Trigger cancels insert

Is it possible for me to write a before insert trigger which will prevent insert if the data doesn't match specific criteria? What would the syntax look like?SQL Server triggers are always AFTER triggers, but you can rollback the insert.

If some condition causes you to want to fail the insert, you can just rollback the transaction

From Books Online:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
 WHERE name = 'employee_insupd' AND type = 'TR')
 DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @.min_lvl tinyint,
 @.max_lvl tinyint,
 @.emp_lvl tinyint,
 @.job_id smallint
SELECT @.min_lvl = min_lvl,
 @.max_lvl = max_lvl,
 @.emp_lvl = i.job_lvl,
 @.job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
 JOIN jobs j ON j.job_id = i.job_id
IF (@.job_id = 1) and (@.emp_lvl <> 10)
BEGIN
 RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
 ROLLBACK TRANSACTION
END
ELSE
IF NOT (@.emp_lvl BETWEEN @.min_lvl AND @.max_lvl)
BEGIN
 RAISERROR ('The level for job_id:%d should be between %d and %d.',
 16, 1, @.job_id, @.min_lvl, @.max_lvl)
 ROLLBACK TRANSACTION
END

|||I just want to add that SQL Server 2000 introduced "INSTEAD OF" triggers. These triggers fire BEFORE the action. Well, more correctly they fire instead of the updating action.

Terri|||True. The name has put me off, and so I have not really ever used them, but in effect they can be used exactly like a before trigger. Cool.

From a design standpoint I would have preferred a true BEFORE UPDATE (INSERT/DELETE) trigger to save me some work in the trigger if I want the transaction to go through, but this is certainly close.

Thanks again.|||does SQL Server not have a "Create or Replace" clause? i notice in the sample code that there is logic to do just that.

if i write an INSTEAD OF trigger, how do i insert into the same table without causing recursion? or is SQL Server smart enough to avoid that? i haven't seen any examples in the help files for what i want to do.