SQL Server “AFTER INSERT” trigger doesn't see the just-inserted row?

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.

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.

Um...so long as you're not doing an insert on or after insert you wouldn't trigger infinite recursion. Here Joel's trying to delete on insert which wouldn't cause the trigger to refire. – BenAlabaster Jan 1 '09 at 19:01 My recollection of the theory is a bit hazy but IIRC actually trying to decide that in the general case is intractable.

You could wind up with mutually recursive triggers or other things that can't be statically evaluated. The 'standard' approach is not to support updating the changing data. – ConcernedOfTunbridgeWells Jan 1 '09 at 19:26 MSSQL allows nested and recursive triggers.Msdn.microsoft.Com/en-us/library/aa258254(SQL.80).

Aspx – Mark Brackett Jan 24 '09 at 6:09 Recursive triggers as discussed in the MSDN article aren't quite the same thing. If you update 'inserted' or 'deleted' it is computationally intractable to decide what should be seen by other triggers. Imagine if two or three or 10 triggers made updates - what is the correct data?

What if the updates (or even whwther an update is made) are contingent on the contents of the rows? Now the correct answer requires the decision mechanism to interpret the triggers. Open Wikipedia and read about the undecidability proof of the Halting Problem.

– ConcernedOfTunbridgeWells Dec 10 '09 at 21:19 Triggers definitely modify incoming data, that's the point. We trust the db server not to go in circles within a table. The danger is in coding a loop between two tables, that's when you get infinite recursio, but even that is hard to do because the operations between two tables are never symmetrical.

– Ken Downs Jan 21 at 17:46.

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.

Thanks, but I found that CHECK constraint may be even better solution – Dmitry Khalatov Jan 1 '09 at 20:14 Yes, CHECK constraint is also good. I assume Joel simplified the nature of his condition, and some complex conditions may not be best implemented in a CHECK constraint. – Bill Karwin Jan 1 '09 at 20:30 Right.It's important to us that the insert NOT fail, because that would crash the process doing the insert.

– Joel Spolsky? Jan 5 '09 at 20:34 It's a tricky problem, because you're setting up a condition where the app thinks it performed an insert, but it didn't happen. Could lead to data integrity problems.

– Bill Karwin Jan 5 '09 at 21:09 5 Bill your solution is a bad practice ina trigger. It will only work if one record is inserted. All triggers must be designed to handle multiple record inserts.

– HLGEM May 8 '09 at 18:20.

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 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).

Jan 1 '09 at 19:09 See my separate answer. +1 to Dmitry. – Bill Karwin Jan 1 '09 at 20:05 1 It turns out the reason we were using a trigger is that the running code didn't handle insert failures well.

In most cases, you're right, we shouldn't use a trigger. But the original question still might be relevant to someone, so I'm accepting the answer that addresses it exactly. – Joel Spolsky?

Jan 2 '09 at 5:26.

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.

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...

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...

Imagine this scenario. 1) Update a row to have a bad numeric 2) then insert valid data. Wouldn't the trigger prevent #2 from ever suceeding?

– JaredPar Jan 1 '09 at 19:14 I guess theoretically you're right. I'll add an addendum to qualify that. – BenAlabaster Jan 1 '09 at 19:23.

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.

2 It's for a spam filter. The spamming process needs to see the record getting inserted successfully, otherwise the spammer will just modify their spam techniques until it does. We don't want the spammer to know there's a REGEXP that catches his spam or he'll work around it.

– Joel Spolsky? Jan 5 '09 at 20:32 2 So - a legitimate case for an intentionally hostile user interface. Good one.

– le dorfier Jan 5 '09 at 20:42.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.