SQL Server AFTER INSERT trigger doesn t see the just-inserted row

Question

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

User · Answer

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

User · Answer

Triggers cannot modify the changed data  Inserted or Deleted  otherwise you could get infinite recursion as the changes invoked the trigger again   One option would be for the trigger to roll back the transaction   Edit  The reason for this is that the standard for SQL is that inserted and deleted rows cannot be modified by the trigger   The underlying reason for is that the modifications could cause infinite recursion   In the general case  this evaluation could involve multiple triggers in a mutually recursive cascade   Having a system intelligently decide whether to allow such updates is computationally intractable  essentially a variation on the halting problem     The accepted solution to this is not to permit the trigger to alter the changing data  although it can roll back the transaction     create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo after insert as     begin         delete inserted          where isnumeric  SomeField    1     end go   Msg 286  Level 16  State 1  Procedure FooInsert  Line 5 The logical tables INSERTED and DELETED cannot be updated    Something like this will roll back the transaction   create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo for insert as     if exists          select 1          from inserted          where isnumeric  SomeField    1  begin               rollback transaction     end go  insert Foo values  1   1    Msg 3609  Level 16  State 1  Line 1 The transaction ended in the trigger  The batch has been aborted

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I found this reference   create trigger myTrigger on SomeTable for insert  as  if  select count         from SomeTable  inserted      where IsNumeric SomeField    1   lt  gt  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

User · Answer

I stumbled across this question looking for details on the sequence of events during an insert statement  amp  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

User · Answer

Triggers cannot modify the changed data  Inserted or Deleted  otherwise you could get infinite recursion as the changes invoked the trigger again   One option would be for the trigger to roll back the transaction   Edit  The reason for this is that the standard for SQL is that inserted and deleted rows cannot be modified by the trigger   The underlying reason for is that the modifications could cause infinite recursion   In the general case  this evaluation could involve multiple triggers in a mutually recursive cascade   Having a system intelligently decide whether to allow such updates is computationally intractable  essentially a variation on the halting problem     The accepted solution to this is not to permit the trigger to alter the changing data  although it can roll back the transaction     create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo after insert as     begin         delete inserted          where isnumeric  SomeField    1     end go   Msg 286  Level 16  State 1  Procedure FooInsert  Line 5 The logical tables INSERTED and DELETED cannot be updated    Something like this will roll back the transaction   create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo for insert as     if exists          select 1          from inserted          where isnumeric  SomeField    1  begin               rollback transaction     end go  insert Foo values  1   1    Msg 3609  Level 16  State 1  Line 1 The transaction ended in the trigger  The batch has been aborted

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I found this reference   create trigger myTrigger on SomeTable for insert  as  if  select count         from SomeTable  inserted      where IsNumeric SomeField    1   lt  gt  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

User · Answer

I stumbled across this question looking for details on the sequence of events during an insert statement  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I found this reference   create trigger myTrigger on SomeTable for insert  as  if  select count         from SomeTable  inserted      where IsNumeric SomeField    1   lt  gt  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

User · Answer

Triggers cannot modify the changed data  Inserted or Deleted  otherwise you could get infinite recursion as the changes invoked the trigger again   One option would be for the trigger to roll back the transaction   Edit  The reason for this is that the standard for SQL is that inserted and deleted rows cannot be modified by the trigger   The underlying reason for is that the modifications could cause infinite recursion   In the general case  this evaluation could involve multiple triggers in a mutually recursive cascade   Having a system intelligently decide whether to allow such updates is computationally intractable  essentially a variation on the halting problem     The accepted solution to this is not to permit the trigger to alter the changing data  although it can roll back the transaction     create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo after insert as     begin         delete inserted          where isnumeric  SomeField    1     end go   Msg 286  Level 16  State 1  Procedure FooInsert  Line 5 The logical tables INSERTED and DELETED cannot be updated    Something like this will roll back the transaction   create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo for insert as     if exists          select 1          from inserted          where isnumeric  SomeField    1  begin               rollback transaction     end go  insert Foo values  1   1    Msg 3609  Level 16  State 1  Line 1 The transaction ended in the trigger  The batch has been aborted

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I found this reference   create trigger myTrigger on SomeTable for insert  as  if  select count         from SomeTable  inserted      where IsNumeric SomeField    1   lt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Triggers cannot modify the changed data  Inserted or Deleted  otherwise you could get infinite recursion as the changes invoked the trigger again   One option would be for the trigger to roll back the transaction   Edit  The reason for this is that the standard for SQL is that inserted and deleted rows cannot be modified by the trigger   The underlying reason for is that the modifications could cause infinite recursion   In the general case  this evaluation could involve multiple triggers in a mutually recursive cascade   Having a system intelligently decide whether to allow such updates is computationally intractable  essentially a variation on the halting problem     The accepted solution to this is not to permit the trigger to alter the changing data  although it can roll back the transaction     create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo after insert as     begin         delete inserted          where isnumeric  SomeField    1     end go   Msg 286  Level 16  State 1  Procedure FooInsert  Line 5 The logical tables INSERTED and DELETED cannot be updated    Something like this will roll back the transaction   create table Foo          FooID int        SomeField varchar  10    go  create trigger FooInsert     on Foo for insert as     if exists          select 1          from inserted          where isnumeric  SomeField    1  begin               rollback transaction     end go  insert Foo values  1   1    Msg 3609  Level 16  State 1  Line 1 The transaction ended in the trigger  The batch has been aborted

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2005

Examples related to triggers