[sql-server] SQL Server "AFTER INSERT" trigger doesn't see the just-inserted row

Consider this trigger:

ALTER TRIGGER myTrigger 
   ON someTable 
   AFTER INSERT
AS BEGIN
  DELETE FROM someTable
         WHERE ISNUMERIC(someField) = 1
END

I've got a table, someTable, and I'm trying to prevent people from inserting bad records. For the purpose of this question, a bad record has a field "someField" that is all numeric.

Of course, the right way to do this is NOT with a trigger, but I don't control the source code... just the SQL database. So I can't really prevent the insertion of the bad row, but I can delete it right away, which is good enough for my needs.

The trigger works, with one problem... when it fires, it never seems to delete the just-inserted bad record... it deletes any OLD bad records, but it doesn't delete the just-inserted bad record. So there's often one bad record floating around that isn't deleted until somebody else comes along and does another INSERT.

Is this a problem in my understanding of triggers? Are newly-inserted rows not yet committed while the trigger is running?

You can reverse the logic. Instead of deleting an invalid row after it has been inserted, write an INSTEAD OF trigger to insert only if you verify the row is valid.

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

If your application doesn't want to handle errors (as Joel says is the case in his app), then don't RAISERROR. Just make the trigger silently not do an insert that isn't valid.

I ran this on SQL Server Express 2005 and it works. Note that INSTEAD OF triggers do not cause recursion if you insert into the same table for which the trigger is defined.

I found this reference:

create trigger myTrigger
on SomeTable
for insert 
as 
if (select count(*) 
    from SomeTable, inserted 
    where IsNumeric(SomeField) = 1) <> 0
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "You can't do that!"  
  end  
/* Otherwise, allow it. */
else
  print "Added successfully."

I haven't tested it, but logically it looks like it should dp what you're after...rather than deleting the inserted data, prevent the insertion completely, thus not requiring you to have to undo the insert. It should perform better and should therefore ultimately handle a higher load with more ease.

Edit: Of course, there is the potential that if the insert happened inside of an otherwise valid transaction that the wole transaction could be rolled back so you would need to take that scenario into account and determine if the insertion of an invalid data row would constitute a completely invalid transaction...

The techniques outlined above describe your options pretty well. But what are the users seeing? I can't imagine how a basic conflict like this between you and whoever is responsible for the software can't end up in confusion and antagonism with the users.

I'd do everything I could to find some other way out of the impasse - because other people could easily see any change you make as escalating the problem.

EDIT:

I'll score my first "undelete" and admit to posting the above when this question first appeared. I of course chickened out when I saw that it was from JOEL SPOLSKY. But it looks like it landed somewhere near. Don't need votes, but I'll put it on the record.

IME, triggers are so seldom the right answer for anything other than fine-grained integrity constraints outside the realm of business rules.

I found this reference:

create trigger myTrigger
on SomeTable
for insert 
as 
if (select count(*) 
    from SomeTable, inserted 
    where IsNumeric(SomeField) = 1) <> 0
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "You can't do that!"  
  end  
/* Otherwise, allow it. */
else
  print "Added successfully."

I haven't tested it, but logically it looks like it should dp what you're after...rather than deleting the inserted data, prevent the insertion completely, thus not requiring you to have to undo the insert. It should perform better and should therefore ultimately handle a higher load with more ease.

Edit: Of course, there is the potential that if the insert happened inside of an otherwise valid transaction that the wole transaction could be rolled back so you would need to take that scenario into account and determine if the insertion of an invalid data row would constitute a completely invalid transaction...

UPDATE: DELETE from a trigger works on both MSSql 7 and MSSql 2008.

I'm no relational guru, nor a SQL standards wonk. However - contrary to the accepted answer - MSSQL deals just fine with both recursive and nested trigger evaluation. I don't know about other RDBMSs.

The relevant options are 'recursive triggers' and 'nested triggers'. Nested triggers are limited to 32 levels, and default to 1. Recursive triggers are off by default, and there's no talk of a limit - but frankly, I've never turned them on, so I don't know what happens with the inevitable stack overflow. I suspect MSSQL would just kill your spid (or there is a recursive limit).

Of course, that just shows that the accepted answer has the wrong reason, not that it's incorrect. However, prior to INSTEAD OF triggers, I recall writing ON INSERT triggers that would merrily UPDATE the just inserted rows. This all worked fine, and as expected.

A quick test of DELETEing the just inserted row also works:

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

You have something else going on here.

I stumbled across this question looking for details on the sequence of events during an insert statement & trigger. I ended up coding some brief tests to confirm how SQL 2016 (EXPRESS) behaves - and thought it would be appropriate to share as it might help others searching for similar information.

Based on my test, it is possible to select data from the "inserted" table and use that to update the inserted data itself. And, of interest to me, the inserted data is not visible to other queries until the trigger completes at which point the final result is visible (at least best as I could test). I didn't test this for recursive triggers, etc. (I would expect the nested trigger would have full visibility of the inserted data in the table, but that's just a guess).

For example - assuming we have the table "table" with an integer field "field" and primary key field "pk" and the following code in our insert trigger:

select @value=field,@pk=pk from inserted
update table set field=@value+1 where pk=@pk
waitfor delay '00:00:15'

We insert a row with the value 1 for "field", then the row will end up with the value 2. Furthermore - if I open another window in SSMS and try: select * from table where pk = @pk

where @pk is the primary key I originally inserted, the query will be empty until the 15 seconds expire and will then show the updated value (field=2).

I was interested in what data is visible to other queries while the trigger is executing (apparently no new data). I tested with an added delete as well:

select @value=field,@pk=pk from inserted
update table set field=@value+1 where pk=@pk
delete from table where pk=@pk
waitfor delay '00:00:15'

Again, the insert took 15sec to execute. A query executing in a different session showed no new data - during or after execution of the insert + trigger (although I would expect any identity would increment even if no data appears to be inserted).

MS-SQL has a setting to prevent recursive trigger firing. This is confirgured via the sp_configure stored proceedure, where you can turn recursive or nested triggers on or off.

In this case, it would be possible, if you turn off recursive triggers to link the record from the inserted table via the primary key, and make changes to the record.

In the specific case in the question, it is not really a problem, because the result is to delete the record, which won't refire this particular trigger, but in general that could be a valid approach. We implemented optimistic concurrency this way.

The code for your trigger that could be used in this way would be:

ALTER TRIGGER myTrigger
    ON someTable
    AFTER INSERT
AS BEGIN
DELETE FROM someTable
    INNER JOIN inserted on inserted.primarykey = someTable.primarykey
    WHERE ISNUMERIC(inserted.someField) = 1
END

Is it possible the INSERT is valid, but that a separate UPDATE is done afterwards that is invalid but wouldn't fire the trigger?

You can reverse the logic. Instead of deleting an invalid row after it has been inserted, write an INSTEAD OF trigger to insert only if you verify the row is valid.

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

If your application doesn't want to handle errors (as Joel says is the case in his app), then don't RAISERROR. Just make the trigger silently not do an insert that isn't valid.

I ran this on SQL Server Express 2005 and it works. Note that INSTEAD OF triggers do not cause recursion if you insert into the same table for which the trigger is defined.

Is it possible the INSERT is valid, but that a separate UPDATE is done afterwards that is invalid but wouldn't fire the trigger?

The techniques outlined above describe your options pretty well. But what are the users seeing? I can't imagine how a basic conflict like this between you and whoever is responsible for the software can't end up in confusion and antagonism with the users.

I'd do everything I could to find some other way out of the impasse - because other people could easily see any change you make as escalating the problem.

EDIT:

I'll score my first "undelete" and admit to posting the above when this question first appeared. I of course chickened out when I saw that it was from JOEL SPOLSKY. But it looks like it landed somewhere near. Don't need votes, but I'll put it on the record.

IME, triggers are so seldom the right answer for anything other than fine-grained integrity constraints outside the realm of business rules.

From the CREATE TRIGGER documentation:

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted

So that at least gives you a way of seeing the new data.

I can't see anything in the docs which specifies that you won't see the inserted data when querying the normal table though...

You can reverse the logic. Instead of deleting an invalid row after it has been inserted, write an INSTEAD OF trigger to insert only if you verify the row is valid.

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

If your application doesn't want to handle errors (as Joel says is the case in his app), then don't RAISERROR. Just make the trigger silently not do an insert that isn't valid.

I ran this on SQL Server Express 2005 and it works. Note that INSTEAD OF triggers do not cause recursion if you insert into the same table for which the trigger is defined.

MS-SQL has a setting to prevent recursive trigger firing. This is confirgured via the sp_configure stored proceedure, where you can turn recursive or nested triggers on or off.

In this case, it would be possible, if you turn off recursive triggers to link the record from the inserted table via the primary key, and make changes to the record.

In the specific case in the question, it is not really a problem, because the result is to delete the record, which won't refire this particular trigger, but in general that could be a valid approach. We implemented optimistic concurrency this way.

The code for your trigger that could be used in this way would be:

ALTER TRIGGER myTrigger
    ON someTable
    AFTER INSERT
AS BEGIN
DELETE FROM someTable
    INNER JOIN inserted on inserted.primarykey = someTable.primarykey
    WHERE ISNUMERIC(inserted.someField) = 1
END

From the CREATE TRIGGER documentation:

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted

So that at least gives you a way of seeing the new data.

I can't see anything in the docs which specifies that you won't see the inserted data when querying the normal table though...

Here's my modified version of Bill's code:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

This lets the insert always succeed, and any bogus records get thrown into your sometableRejects where you can handle them later. It's important to make your rejects table use nvarchar fields for everything - not ints, tinyints, etc - because if they're getting rejected, it's because the data isn't what you expected it to be.

This also solves the multiple-record insert problem, which will cause Bill's trigger to fail. If you insert ten records simultaneously (like if you do a select-insert-into) and just one of them is bogus, Bill's trigger would have flagged all of them as bad. This handles any number of good and bad records.

I used this trick on a data warehousing project where the inserting application had no idea whether the business logic was any good, and we did the business logic in triggers instead. Truly nasty for performance, but if you can't let the insert fail, it does work.

Is it possible the INSERT is valid, but that a separate UPDATE is done afterwards that is invalid but wouldn't fire the trigger?

From the CREATE TRIGGER documentation:

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted

So that at least gives you a way of seeing the new data.

I can't see anything in the docs which specifies that you won't see the inserted data when querying the normal table though...

The techniques outlined above describe your options pretty well. But what are the users seeing? I can't imagine how a basic conflict like this between you and whoever is responsible for the software can't end up in confusion and antagonism with the users.

I'd do everything I could to find some other way out of the impasse - because other people could easily see any change you make as escalating the problem.

EDIT:

I'll score my first "undelete" and admit to posting the above when this question first appeared. I of course chickened out when I saw that it was from JOEL SPOLSKY. But it looks like it landed somewhere near. Don't need votes, but I'll put it on the record.

IME, triggers are so seldom the right answer for anything other than fine-grained integrity constraints outside the realm of business rules.

I think you can use CHECK constraint - it is exactly what it was invented for.

ALTER TABLE someTable 
ADD CONSTRAINT someField_check CHECK (ISNUMERIC(someField) = 1) ;

My previous answer (also right by may be a bit overkill):

I think the right way is to use INSTEAD OF trigger to prevent the wrong data from being inserted (rather than deleting it post-factum)

The techniques outlined above describe your options pretty well. But what are the users seeing? I can't imagine how a basic conflict like this between you and whoever is responsible for the software can't end up in confusion and antagonism with the users.

I'd do everything I could to find some other way out of the impasse - because other people could easily see any change you make as escalating the problem.

EDIT:

I'll score my first "undelete" and admit to posting the above when this question first appeared. I of course chickened out when I saw that it was from JOEL SPOLSKY. But it looks like it landed somewhere near. Don't need votes, but I'll put it on the record.

IME, triggers are so seldom the right answer for anything other than fine-grained integrity constraints outside the realm of business rules.

Here's my modified version of Bill's code:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

This lets the insert always succeed, and any bogus records get thrown into your sometableRejects where you can handle them later. It's important to make your rejects table use nvarchar fields for everything - not ints, tinyints, etc - because if they're getting rejected, it's because the data isn't what you expected it to be.

This also solves the multiple-record insert problem, which will cause Bill's trigger to fail. If you insert ten records simultaneously (like if you do a select-insert-into) and just one of them is bogus, Bill's trigger would have flagged all of them as bad. This handles any number of good and bad records.

I used this trick on a data warehousing project where the inserting application had no idea whether the business logic was any good, and we did the business logic in triggers instead. Truly nasty for performance, but if you can't let the insert fail, it does work.

Your "trigger" is doing something that a "trigger" is not suppose to be doing. You can simple have your Sql Server Agent run

DELETE FROM someTable
WHERE ISNUMERIC(someField) = 1

every 1 second or so. While you're at it, how about writing a nice little SP to stop the programming folk from inserting errors into your table. One good thing about SP's is that the parameters are type safe.

I found this reference:

create trigger myTrigger
on SomeTable
for insert 
as 
if (select count(*) 
    from SomeTable, inserted 
    where IsNumeric(SomeField) = 1) <> 0
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "You can't do that!"  
  end  
/* Otherwise, allow it. */
else
  print "Added successfully."

I haven't tested it, but logically it looks like it should dp what you're after...rather than deleting the inserted data, prevent the insertion completely, thus not requiring you to have to undo the insert. It should perform better and should therefore ultimately handle a higher load with more ease.

Edit: Of course, there is the potential that if the insert happened inside of an otherwise valid transaction that the wole transaction could be rolled back so you would need to take that scenario into account and determine if the insertion of an invalid data row would constitute a completely invalid transaction...

Here's my modified version of Bill's code:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

This lets the insert always succeed, and any bogus records get thrown into your sometableRejects where you can handle them later. It's important to make your rejects table use nvarchar fields for everything - not ints, tinyints, etc - because if they're getting rejected, it's because the data isn't what you expected it to be.

This also solves the multiple-record insert problem, which will cause Bill's trigger to fail. If you insert ten records simultaneously (like if you do a select-insert-into) and just one of them is bogus, Bill's trigger would have flagged all of them as bad. This handles any number of good and bad records.

I used this trick on a data warehousing project where the inserting application had no idea whether the business logic was any good, and we did the business logic in triggers instead. Truly nasty for performance, but if you can't let the insert fail, it does work.

I stumbled across this question looking for details on the sequence of events during an insert statement & trigger. I ended up coding some brief tests to confirm how SQL 2016 (EXPRESS) behaves - and thought it would be appropriate to share as it might help others searching for similar information.

Based on my test, it is possible to select data from the "inserted" table and use that to update the inserted data itself. And, of interest to me, the inserted data is not visible to other queries until the trigger completes at which point the final result is visible (at least best as I could test). I didn't test this for recursive triggers, etc. (I would expect the nested trigger would have full visibility of the inserted data in the table, but that's just a guess).

For example - assuming we have the table "table" with an integer field "field" and primary key field "pk" and the following code in our insert trigger:

select @value=field,@pk=pk from inserted
update table set field=@value+1 where pk=@pk
waitfor delay '00:00:15'

We insert a row with the value 1 for "field", then the row will end up with the value 2. Furthermore - if I open another window in SSMS and try: select * from table where pk = @pk

where @pk is the primary key I originally inserted, the query will be empty until the 15 seconds expire and will then show the updated value (field=2).

I was interested in what data is visible to other queries while the trigger is executing (apparently no new data). I tested with an added delete as well:

select @value=field,@pk=pk from inserted
update table set field=@value+1 where pk=@pk
delete from table where pk=@pk
waitfor delay '00:00:15'

Again, the insert took 15sec to execute. A query executing in a different session showed no new data - during or after execution of the insert + trigger (although I would expect any identity would increment even if no data appears to be inserted).

I think you can use CHECK constraint - it is exactly what it was invented for.

ALTER TABLE someTable 
ADD CONSTRAINT someField_check CHECK (ISNUMERIC(someField) = 1) ;

My previous answer (also right by may be a bit overkill):

I think the right way is to use INSTEAD OF trigger to prevent the wrong data from being inserted (rather than deleting it post-factum)

UPDATE: DELETE from a trigger works on both MSSql 7 and MSSql 2008.

I'm no relational guru, nor a SQL standards wonk. However - contrary to the accepted answer - MSSQL deals just fine with both recursive and nested trigger evaluation. I don't know about other RDBMSs.

The relevant options are 'recursive triggers' and 'nested triggers'. Nested triggers are limited to 32 levels, and default to 1. Recursive triggers are off by default, and there's no talk of a limit - but frankly, I've never turned them on, so I don't know what happens with the inevitable stack overflow. I suspect MSSQL would just kill your spid (or there is a recursive limit).

Of course, that just shows that the accepted answer has the wrong reason, not that it's incorrect. However, prior to INSTEAD OF triggers, I recall writing ON INSERT triggers that would merrily UPDATE the just inserted rows. This all worked fine, and as expected.

A quick test of DELETEing the just inserted row also works:

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

You have something else going on here.

I think you can use CHECK constraint - it is exactly what it was invented for.

ALTER TABLE someTable 
ADD CONSTRAINT someField_check CHECK (ISNUMERIC(someField) = 1) ;

My previous answer (also right by may be a bit overkill):

I think the right way is to use INSTEAD OF trigger to prevent the wrong data from being inserted (rather than deleting it post-factum)

Here's my modified version of Bill's code:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

This lets the insert always succeed, and any bogus records get thrown into your sometableRejects where you can handle them later. It's important to make your rejects table use nvarchar fields for everything - not ints, tinyints, etc - because if they're getting rejected, it's because the data isn't what you expected it to be.

This also solves the multiple-record insert problem, which will cause Bill's trigger to fail. If you insert ten records simultaneously (like if you do a select-insert-into) and just one of them is bogus, Bill's trigger would have flagged all of them as bad. This handles any number of good and bad records.

I used this trick on a data warehousing project where the inserting application had no idea whether the business logic was any good, and we did the business logic in triggers instead. Truly nasty for performance, but if you can't let the insert fail, it does work.

UPDATE: DELETE from a trigger works on both MSSql 7 and MSSql 2008.

I'm no relational guru, nor a SQL standards wonk. However - contrary to the accepted answer - MSSQL deals just fine with both recursive and nested trigger evaluation. I don't know about other RDBMSs.

The relevant options are 'recursive triggers' and 'nested triggers'. Nested triggers are limited to 32 levels, and default to 1. Recursive triggers are off by default, and there's no talk of a limit - but frankly, I've never turned them on, so I don't know what happens with the inevitable stack overflow. I suspect MSSQL would just kill your spid (or there is a recursive limit).

Of course, that just shows that the accepted answer has the wrong reason, not that it's incorrect. However, prior to INSTEAD OF triggers, I recall writing ON INSERT triggers that would merrily UPDATE the just inserted rows. This all worked fine, and as expected.

A quick test of DELETEing the just inserted row also works:

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

You have something else going on here.

You can reverse the logic. Instead of deleting an invalid row after it has been inserted, write an INSTEAD OF trigger to insert only if you verify the row is valid.

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

If your application doesn't want to handle errors (as Joel says is the case in his app), then don't RAISERROR. Just make the trigger silently not do an insert that isn't valid.

I ran this on SQL Server Express 2005 and it works. Note that INSTEAD OF triggers do not cause recursion if you insert into the same table for which the trigger is defined.

Is it possible the INSERT is valid, but that a separate UPDATE is done afterwards that is invalid but wouldn't fire the trigger?

From the CREATE TRIGGER documentation:

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted

So that at least gives you a way of seeing the new data.

I can't see anything in the docs which specifies that you won't see the inserted data when querying the normal table though...

Your "trigger" is doing something that a "trigger" is not suppose to be doing. You can simple have your Sql Server Agent run

DELETE FROM someTable
WHERE ISNUMERIC(someField) = 1

every 1 second or so. While you're at it, how about writing a nice little SP to stop the programming folk from inserting errors into your table. One good thing about SP's is that the parameters are type safe.

UPDATE: DELETE from a trigger works on both MSSql 7 and MSSql 2008.

I'm no relational guru, nor a SQL standards wonk. However - contrary to the accepted answer - MSSQL deals just fine with both recursive and nested trigger evaluation. I don't know about other RDBMSs.

The relevant options are 'recursive triggers' and 'nested triggers'. Nested triggers are limited to 32 levels, and default to 1. Recursive triggers are off by default, and there's no talk of a limit - but frankly, I've never turned them on, so I don't know what happens with the inevitable stack overflow. I suspect MSSQL would just kill your spid (or there is a recursive limit).

Of course, that just shows that the accepted answer has the wrong reason, not that it's incorrect. However, prior to INSTEAD OF triggers, I recall writing ON INSERT triggers that would merrily UPDATE the just inserted rows. This all worked fine, and as expected.

A quick test of DELETEing the just inserted row also works:

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

You have something else going on here.